Insert space between first and last names

[Yippy_Kiyey]

Hello,

I need a formula or an easy way to separate the first and last names if the data looks like this.

JohnSmith
GeorgeBush
SimonCowell

Thank you much in advance

Rey

 

[mikerickson]

This CSE formula should do what you want
=REPLACE(A1, 1+MIN(FIND(CHAR(64+COLUMN($A$1:$Z$1)),MID(A1,2,255)&(CHAR(64+COLUMN($A$1:$Z$1))))), 0, " ")

It should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Yippy_Kiyey]

Sir,
The formula worked like a charm. I hope you don't mind if I throw a curve ball. How about if the data looks like this. I want to seprate the first, middle and last names.


BettyAnnLou
JohnJamesMcEnroe

Thanks again
Rey

 

[mikerickson]

What version of excel are you using? Do you have the ISERROR function?

I don't have time to expand on this idea right now, but the plan is

MID(A1, ROW(1:100), 1) is an array of characters of the string

CODE(MID(...)) is their ASCII code

(64 < CODE(...))AND(CODE(...) < 86))*ROW(1:100) is an array whose entry is 0 if the character is not upper case and the index of the character if it is upper case.
i.e. if A1 is "abcDefGhiJk" then then array is {0;0;0;4;0;0;7;0;0;10;0}

That array, plus LARGE, allows one to find the n'th upper case letter in a string.

 

[T. Valko]

What version of excel are you using? Do you have the ISERROR function?

I don't have time to expand on this idea right now, but the plan is

MID(A1, ROW(1:100), 1) is an array of characters of the string

CODE(MID(...)) is their ASCII code

(64 < CODE(...))AND(CODE(...) < 86))*ROW(1:100) is an array whose entry is 0 if the character is not upper case and the index of the character if it is upper case.
i.e. if A1 is "abcDefGhiJk" then then array is {0;0;0;4;0;0;7;0;0;10;0}

That array, plus LARGE, allows one to find the n'th upper case letter in a string.

I don't see how this could be done with 100% accuracy.

How do you handle 2 and 3 word first/last names?

BillyBobThornton
MartinStLouis
OscarDeLaHoya

 

[Yippy_Kiyey]

Sirs:

Thank you for you thoughts about this. I am using Excel 2010.
I see why it can't be 100% accurate because of names like OscarRayDeLaHoya. As long as the formula will work on names like BillyBobThorton, that will be great.

Thank you,

Rey

 

[mikerickson]

Sirs:

Thank you for you thoughts about this. I am using Excel 2010.
I see why it can't be 100% accurate because of names like OscarRayDeLaHoya. As long as the formula will work on names like BillyBobThorton, that will be great.

Thank you,

Rey

I just had a revelation.

You have a formula that will do JohnSmith to John Smith.
Put the formula in B1, drag it right to C1 and it will turn

BillyBobThornton > Billy BobThorton > Billy Bob Thorton

 

[Yippy_Kiyey]

Of course, that make sense. I will give a shot.

 

[BenMiller]

I just had a revelation.

You have a formula that will do JohnSmith to John Smith.
Put the formula in B1, drag it right to C1 and it will turn

BillyBobThornton > Billy BobThorton > Billy Bob Thorton

The formula you originally gave won't work for that though ... it just puts another space in the original

 

[NewbieWonCanoobie]

I just had a revelation.

You have a formula that will do JohnSmith to John Smith.
Put the formula in B1, drag it right to C1 and it will turn

BillyBobThornton > Billy BobThorton > Billy Bob Thorton

The formula you originally gave won't work for that though ... it just puts another space in the original

I was hoping it would work too. But I guess we can't get everything we want.


Kindest regards,
NewbieWon