Multiple words in Column A - Split those into two columns


Me again.

I have an excel text column with multiple words in it. A space is between each word.

Such as....

Column A Row 1  Apple banana orange
Column A Row 2   1 Dog cat
Column A Row 3  Experts Exchange is 2 great

I would like a formula that makes those words into two columns

Column B would have the only the first word (or number)
Column C would have the remaining words (and any numbers)

So the result would be in the above example

Column B  Row 1 Apple          Column C Row 1 banana orange
Column B  Row 2 1                 Column C Row 2  Dog cat
Column B  Row3  Experts        Column C Row 3  is 2 great


Rowby GorenAsked:
Who is Participating?
Hi, rowby.

Assuming that the data is clean - always a space with data before and after - then the attached should do it for you...
B1     =MID(A1,1,FIND(" ",A1,1)-1)
C1     =MID(A1,FIND(" ",A1,1)+1,9999)

Edit: The following versions will also handle no data and missing spaces (at the cost of using TRIM() and so dropping extra spaces in Column C)...
B1     =MID(A1&" ",1,FIND(" ",A1&" ",1)-1)
C1     =TRIM(MID(A1,FIND(" ",A1&" ",1)+1,9999))

Let me know if you need different error-handling (and which version of Excel you're usng.)

Rowby GorenAuthor Commented:
Hi Brian,

I'm using Windows Excel Office 2007.

I'll try both of your versions on my list and see if it's clean enough to use your formula(s) "as is".

I'll be trying them in the morning.


Thanks, Rowby. Unless you actually want an error message in the cell(s) , the second set should be fine.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Rowby GorenAuthor Commented:
Thanks!  #2 worked perfectly!

Appreciate your help!

Rowby GorenAuthor Commented:
Thanks again!
Thanks, rowby, Glad to help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.