Is there an opposite function to CONCATENATE?

[NeilF]

I have discovered the useful CONCATENATE function, which I have typically been using to combine 2 cells with surname (b1) and first name (a1).

eg. =CONCATENATE(B1, ", ", A1) returns in "Smith, John" where cell A1 contained "John" and B1 contained "Smith".

My question is whether there is a function (or a method) by which that process could be reversed. ie. To take a cell containing "Smith, John" and return 2 adjacent cells, one containing John and next containing Smith.

Sounds simple, but I suspect it is far from easy....!

Thanks

Neil

 

[Momentman]

You can use Text To Columns, its on the Data Tab

 

[Andrew Poulsom]

VBA has a Split function, but there isn't an equivalent Excel function, unfortunately. You can use formulas like:

=LEFT(A1,FIND(",",A1)-1)

=TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))

 

[NeilF]

Thank you both.

I'm not really at VBA level(!), but I've just tried using Text to Columns, which I didn't know about previously (thank you Momentman).

That seems to work very well. The one legacy thing it leaves me with is the space at the start of the first name cell. Is there an easy way to strip that out (or ideally have it removed as part of the Text to Columns process)?

Thanks

Neil

 

[Andrew Poulsom]

Use Comma and Space as the delimiters.

 

[NeilF]

Ah, yes. That makes sense. Many thanks Andrew.

Neil