Split text and value among columns by using functions


Column A is populated with codes that have a certain format. I need to split this format into two columns.

A code looks as following:

5, 6, or a 7 digit number and then a description. So I need to have these digit numbers in column B and a description in column C.
 (example 84100 PHOSPHORUS)

Please advise!

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
see the steps in the screenshot  . start selecting your data and then go to Data tab and select Text to Column

 1.PNG then  un-tick all and tick mark Space and then click next and finish
dsackerContract ERP Admin/ConsultantCommented:
Assuming you have "84100 PHOSPHORUS" in cell A2, put this in cell B2:

=LEFT(A2, SEARCH(" ", A2) - 1)
Put this in cell C2:

=MID(A2, SEARCH(" ", A2) + 1, 80)
After that, simply copy the formula down columns B and C.

This solution also assumes that a space will consistently be your delimiter between the two values in column A.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamMicrosoft Excel ExpertCommented:
and if you want to do it with formula then see attached example.

this formula is bulletproof :-)  it will eliminate the risk of number or text being in different sequence in a string
LadkissonAuthor Commented:
Love it! Thank you so much!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.