Excel - Splitting Text Into 2 Columns 2

[ballbearing]

I am certain this has been asked before but can't find the right words for searching.

A B C
Name:John
LastName:Smith

What formula would be needed to split everything left of the colon into column B and right of the colon into column C. I would like to end up with:

A B C
Name:John Name John
LastName:Smith LastName Smith

 

[Rybrookar]

Formulae to use:

1 A B C
2 name:John =left(A2,4) =mid(a2,6,99)
3 lastname:smith =left(a3,8) =mid(a3,10,99)

 

[SkipVought]

Hi,

Do you REALLY want to keep the original in column A?

I'd use Data>Text to columns - DELIMITED, using eh COLON as the delimiter. This will give you
[tt]
Name John
LastName Smith
[/tt]
What Rybrookar was trying to do with a formula...
[tt]
B C
=LEFT($A1,FIND(":",$A1)-1) =RIGHT($A1,LEN($A1)-FIND(":",$A1))

[/tt]




Skip,
[sub]
[red][/red]
[/sub]

 

[ballbearing]

Hmmm...good thought, Skip. I'll try it both ways and see what works best.

Gotta tell you guys...get away from this stuff for a time and it's amazing how one forgets even the simplest things! Thanks to both Rybrookar and Skip!

 

[yogia]

Hi ballbearing:

To add to Skip's suggestion of using DATA>Text_to_Columns using : as delimiter, if you do want to keep the original entries in column A, then in using DATA>Text_to_Columns ... in Step3 of the parsing process delineate Destination: as $B$1 -- results are depicted in the following illustration ...

[img]http://www.energyefficientbuild.com/images/misc-jeanie001.gif[/img]

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com