I have downloaded a list of companies from a system into Excel. The data looks like this:
There are over 10,000 rows of data. All of them have similar format 6 characters (alpha or numeric) followed by a blank space, then "-" then blank space then the company name. Company name can be anywhere from 2 characters to 150 characters long.
On a separate tab, I have a couple hundreds company codes (i.e., first six characters) in one column. I need to get the matching company name for that code in the next column. Hence, I am trying to find a formula (vlookup, xlookup, simple lookup, or any other formula) to match the code and extract the company names from the list shown above.
Here is the end result I am trying to get:
I have tried the Text to column feature of Excel and then used the vlookup formula to get the required results. It worked but I am trying to avoid extra steps and see if this can be done using a formula. I do not know VBA so do not want to go in that direction.
Any help is greatly appreciated.
Thanks in advance.
PS: I am using Office 365 if that helps.
There are over 10,000 rows of data. All of them have similar format 6 characters (alpha or numeric) followed by a blank space, then "-" then blank space then the company name. Company name can be anywhere from 2 characters to 150 characters long.
On a separate tab, I have a couple hundreds company codes (i.e., first six characters) in one column. I need to get the matching company name for that code in the next column. Hence, I am trying to find a formula (vlookup, xlookup, simple lookup, or any other formula) to match the code and extract the company names from the list shown above.
Here is the end result I am trying to get:
I have tried the Text to column feature of Excel and then used the vlookup formula to get the required results. It worked but I am trying to avoid extra steps and see if this can be done using a formula. I do not know VBA so do not want to go in that direction.
Any help is greatly appreciated.
Thanks in advance.
PS: I am using Office 365 if that helps.