All solutions COLUMNS Expert Solution – Excel COLUMN Problems

Excel - COLUMN Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc