Split 2 names within a cell in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know of a way to insert a space in 1 excel cell i.e. FrankJones -
insert space - Frank Jones
Thanks
 
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)
 
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
 
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
 
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
 
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...
 
Didn't the original post say to insert in the ORIGINAL cell.

"insert a space in 1 excel cell "
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split surname from end of name data 4
Split column contents (de-concantenate) 7
Can cells be split ? 5
Word Removing unwanted "lines" within a MS Word 10 table 1
Work with cell contents? 2
Removing unwanted "lines" within a MS Word 10 table 2
Excel Question on Formatting Cell and Adding a Row in Excel 2013 1
How to remove space from a cell. 15

Back
Top