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))),RO W(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)



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

That's pretty slick.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Bob Phillips" wrote:

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))),RO W(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)



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

Thanks Bob - problem solved.

Liz

"Bob Phillips" wrote:

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))),RO W(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)



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

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

--
Don Guillett
SalesAid Software

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

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

"insert a space in 1 excel cell "
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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

--
Don Guillett
SalesAid Software

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

On Thu, 1 Feb 2007 08:15:00 -0800, LBristow
wrote:

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

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

On Thu, 01 Feb 2007 12:40:08 -0500, Ron Rosenfeld
wrote:

On Thu, 1 Feb 2007 08:15:00 -0800, LBristow
wrote:

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

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

Or, a little simpler:

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


--ron