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

Participant · 10 May 2008 at 20:15

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

.

Participant · 10 May 2008 at 21:26

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?

Participant · 10 May 2008 at 21:55

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

.

Participant · 11 May 2008 at 08:43

Aod said:
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

.