Solved

Multiple words in Column A - Split those into two columns

Posted on 2013-01-25
6
236 Views
Last Modified: 2013-01-26
Hello

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

Thanks!

Rowby
0
Comment
Question by:Rowby Goren
  • 3
  • 3
6 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38821353
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.)

Regards,
Brian.
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38821396
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38821421
Thanks, Rowby. Unless you actually want an error message in the cell(s) , the second set should be fine.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 9

Author Comment

by:Rowby Goren
ID: 38822189
Thanks!  #2 worked perfectly!

Appreciate your help!

Rowby
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 38822192
Thanks again!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38822218
Thanks, rowby, Glad to help!
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question