Split 2 names within a cell in excel

L

LBristow

Does anyone know of a way to insert a space in 1 excel cell i.e. FrankJones -
insert space - Frank Jones
Thanks
 
B

Bob Phillips

Put this array formula in B1

=MIN(IF(EXACT(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1),UPPER(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))),ROW(INDIRECT("A2:A"&LEN(A1)))))

as an array formula, it has to be entered with Ctrl-Shift-Enter, not just
Enter, and in C1

=LEFT(A1,B1-1)&" "&RIGHT(A1,LEN(A1)-B1+1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
D

Don Guillett

Sub finducase()
For Each c In Range("a4:a7")
For i = 2 To Len(c)
If Mid(c, i, 1) = UCase(Mid(c, i, 1)) Then x = i - 1
Next i
c.Value = Left(c, x) & " " & Right(c, Len(c) - x)
Next c
End Sub
 
R

Ron Rosenfeld

Does anyone know of a way to insert a space in 1 excel cell i.e. FrankJones -
insert space - Frank Jones
Thanks
Click to expand...

And another method using Regular Expressions:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this formula to insert a space prior to the last capital letter in the
sequence.

=REGEX.SUBSTITUTE(A1,"([A-Z])([a-z]+$)"," [1][2]")
--ron
 
R

Ron Rosenfeld

Does anyone know of a way to insert a space in 1 excel cell i.e. FrankJones -
insert space - Frank Jones
Thanks
Click to expand...

And another method using Regular Expressions:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this formula to insert a space prior to the last capital letter in the
sequence.

=REGEX.SUBSTITUTE(A1,"([A-Z])([a-z]+$)"," [1][2]")
--ron
Click to expand...

Or, a little simpler:

=REGEX.SUBSTITUTE(A1,"([A-Z][a-z]+$)"," [1]")


--ron
 
L

LBristow

Thanks Bob - problem solved.

Liz

Bob Phillips said:
Put this array formula in B1

=MIN(IF(EXACT(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1),UPPER(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))),ROW(INDIRECT("A2:A"&LEN(A1)))))

as an array formula, it has to be entered with Ctrl-Shift-Enter, not just
Enter, and in C1

=LEFT(A1,B1-1)&" "&RIGHT(A1,LEN(A1)-B1+1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
D

Don Guillett

Didn't the original post say to insert in the ORIGINAL cell.

"insert a space in 1 excel cell "
 
Top