Is there a formula in Excel that identifies capital letters and inserts a space?

[Participant]

Hi y'all

If I have a load of names that someone had brilliantly removed the spaces from (but that still had the upper case letters in them), how would I go about automatically inserting a space before each capital using formulae?

I'm thinking along the lines of FIND, UPPER, SEARCH etc but I can't get it to work.

For example, convert

JohnSmith
SusanJones

into

John Smith
Susan Jones

without having to do F2 space enter hundreds of times! Many thanks .

 

[Namsnik]

Hi |Robbie,
I Reckon the easiest way, for a one-off would be:
Edit, replace
A
A
Replace All
B
B
Replace All
etc

Now the first name in the cell will start with a space.
If all the names are in column A then in Column B
=Trim(A1)

Then copy and PasteSpecial to values.

If you are going to have to repeat this then put the macro recorder on...

 

[Participant]

Hi Namsnik and thanks for the reply.

However, I'm struggling with your solution. Replacing A, B, etc. with what?

And if I wanted the first name in each cell to start with a space, I would just do =(A1&" "). And even when the first name starts with a space, I don't see how that would help me split the first name and the surname?

 

[crazyDAJT]

i think he means

replace A with (space) A

26 times

but thats not ideal as it will put a space at the front of peoples names

EDIT: Namsnik solved that, good lateral thinking there

 

[Participant]

I understand now! Thanks, it works great! Good knowledge and sorry for being a 'tard in my first reply .

 

[Aod]

you could use the macro recorder and assign the thing to F11 or F12, and just hold it down untill its finished?

 

[Participant]

you could use the macro recorder and assign the thing to F11 or F12, and just hold it down untill its finished?

Namsnik said this in his reply .