Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Multiple words in Column A - Split those into two columns

Posted on 2013-01-25
6
Medium Priority
?
248 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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

879 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