Question description:
This user has given permission to use the problem statement for this
blog.
I need to create a formula that extracts the prefix from column A
Solved by I. Q. in 42 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
02/07/2018 - 12:26
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User
02/07/2018 - 12:26
Great
Excelchat Expert
02/07/2018 - 12:27
Okay, it seems you need help in extracting the prefixes of items in column A. Will it be possible for you to provide a sample data we could work on? You can have them entered in the document preview. Thanks!
User
02/07/2018 - 12:27
Yep
User
02/07/2018 - 12:27
I will send it to you now
Excelchat Expert
02/07/2018 - 12:28
That would be nice!
Excelchat Expert
02/07/2018 - 12:29
I'll wait for your uploaded data so we could get a better grasp of the concern.
Excelchat Expert
02/07/2018 - 12:32
Hi! Any luck so far in uploading the document? You can use the clipboard icon to the right of the chatbox to select your document and upload it.
User
02/07/2018 - 12:34
one second please
Excelchat Expert
02/07/2018 - 12:35
Okay, will wait for your upload.
User
02/07/2018 - 12:35
its not allowing me to upload as a doc
Excelchat Expert
02/07/2018 - 12:36
Oh, you need to save it in Excel. Or, you can just copy+paste sample data in our document preview for us to work on.
Excelchat Expert
02/07/2018 - 12:36
The file extension in Excel should be .xlsx or .xls
Excelchat Expert
02/07/2018 - 12:37
Or even .csv. However, our system does not allow .xlsm
Excelchat Expert
02/07/2018 - 12:38
Just to clarify, your data is in Excel, correct?
User
02/07/2018 - 12:38
It is not allowing me to open
User
02/07/2018 - 12:38
in the preview box
User
02/07/2018 - 12:38
Can I come back to you in
Excelchat Expert
02/07/2018 - 12:39
Is your file saved in Excel
Excelchat Expert
02/07/2018 - 12:40
Have you tried uploading your Excel file using the clipboard to the right of this chat?
User
02/07/2018 - 12:40
Yep
User
02/07/2018 - 12:41
Can I come back and we do this again
Excelchat Expert
02/07/2018 - 12:42
Unfortunately, we may lose this session in that case. You can upload your file via google drive or dropbox, then send me a link to the file.
User
02/07/2018 - 12:43
https://docs.google.com/document/d/10ZtXIPJGhWYFufUpg2aGCi_snHs5GfTkcJbNC2Bj3eI/edit?usp=sharing
User
02/07/2018 - 12:43
have a look and let me know please
Excelchat Expert
02/07/2018 - 12:46
Oh I see. It seems your Excel file is an attachment to an email. First, you need to download the file in your local drive. you can click on the download button the upper right of your screen as shown
[Uploaded an Excel file]
User
02/07/2018 - 12:47
I cannot download
User
02/07/2018 - 12:47
can you use the information I have to help?
Excelchat Expert
02/07/2018 - 12:48
I can see them, but they're screenshots of the file. We would need the file to incorporate the formulas and get the prefixes within them.
Excelchat Expert
02/07/2018 - 12:50
An alternative method would be clicking on the "Open with" and selecting "Google sheets"
Excelchat Expert
02/07/2018 - 12:51
Please refer to the attached image. After you click that button, you have the option to select "Google sheets". Select this one then you can now share the link with me.
[Uploaded an Excel file]
User
02/07/2018 - 12:54
https://docs.google.com/spreadsheets/d/1bKkgRGcwJu9Gtc5KBUsSl2CSQeV6e7DE0ZtAeyreir0/edit?usp=sharing
User
02/07/2018 - 12:57
I have shared the link with you
User
02/07/2018 - 12:57
we do not have much time
Excelchat Expert
02/07/2018 - 12:58
Nice! Let me open the file quick and let's answer your concern.
Excelchat Expert
02/07/2018 - 12:59
Can you see the file in the document preview?
User
02/07/2018 - 12:59
yes
User
02/07/2018 - 12:59
Can you help me answer all questions please
Excelchat Expert
02/07/2018 - 01:00
Oh, we are actually bound by certain policies in here, one of which is the one question per session policy. As it stands now, we can only answer your initial query of extracting the prefixes of the part numbers in your file.
User
02/07/2018 - 01:00
ok
Excelchat Expert
02/07/2018 - 01:01
Very much apologies on this, but we are regularly audited and our accounts may get revoked if we violate certain rules. However, you can always make sessions with us if you have any other questions related to Excel. For now, let's focus on the PREFIX problem. :)
User
02/07/2018 - 01:01
no problem
Excelchat Expert
02/07/2018 - 01:02
Okay, already done. If you look at cells E6:E15, I've inserted a LEFT function that extracts the prefix of the part numbers as per the question. :)
Excelchat Expert
02/07/2018 - 01:03
Basically, the formula is =LEFT(A6,4). What this formula does is it first gets the data on cell A6, and gets the four first characters of that data. This allows us to EXTRACT the prefix as per the description in question 1. :)
Excelchat Expert
02/07/2018 - 01:04
After we have established this formula in cell E6, we just need to drag it down until cell E15, which would automatically do the same for the cells A7 to A15.
Excelchat Expert
02/07/2018 - 01:06
Were you able to understand the solution so far? :)
User
02/07/2018 - 01:06
yep
Excelchat Expert
02/07/2018 - 01:06
Btw, you can download a copy of the document preview by going to File > Download as > Microsoft Excel (.xlsx). The formulas will be downloaded as well. :)
Excelchat Expert
02/07/2018 - 01:07
If all is good, you can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great rating after this. Thanks so much for using Got it pro and have a nice day! :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
