I would like to split names in a cell. I would like AlphDataCompany in one
cell to be moved/split to Alpha Data Company.
Is the a way of doing this?
Brian

Do you want them to be in the same cell with a space between or do you want
them in seperate cells?

Is there other data like your example that needs to be seperated? If so can
you give a couple more sample names?

"Brian" wrote:

I would like to split names in a cell. I would like AlphDataCompany in one
cell to be moved/split to Alpha Data Company.
Is the a way of doing this?
Brian

If your request to to take text and insert a single space before each upper
case letter then enter and run:

Sub spaceit()
For Each r In Selection
s = r.Value
l = Len(s)
sout = ""
For i = 1 To l
spart = Mid(s, i, 1)
If UCase(spart) = spart Then
sout = sout & " " & spart
Else
sout = sout & spart
End If
Next
r.Value = sout
Next
End Sub
--
Gary''s Student


"Brian" wrote:

I would like to split names in a cell. I would like AlphDataCompany in one
cell to be moved/split to Alpha Data Company.
Is the a way of doing this?
Brian

Here's something to try:

With
A1: (a company name, in ProperName text format)
Example: AlphDataCompany

Then...try these ARRAY FORMULAS*
First capitalized word
B1:
=LEFT(A1,LEN(A1)-LARGE(NOT(EXACT(MID(A1,ROW($A$1:$A$100),1),MID(LOW ER(A1),ROW($A$1:$A$100),1)))*LEN(A1)-ROW($A$1:$A$100),2)-1)

Second capitalized word
C1:
=SUBSTITUTE(LEFT(A1,LEN(A1)-LARGE(NOT(EXACT(MID(A1,ROW($A$1:$A$100),1),MID(LOW ER(A1),ROW($A$1:$A$100),1)))*LEN(A1)-ROW($A$1:$A$100),3)-1),B1,"")

Third capitalized word
D1:
=SUBSTITUTE(LEFT(A1,LEN(A1)-LARGE(NOT(EXACT(MID(A1,ROW($A$1:$A$100),1),MID(LOW ER(A1),ROW($A$1:$A$100),1)))*LEN(A1)-ROW($A$1:$A$100),4)-1),B1&C1,"")

Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note_2: Since text wrap will impact the display, there are NO spaces in
those formulas

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Brian" wrote:

I would like to split names in a cell. I would like AlphDataCompany in one
cell to be moved/split to Alpha Data Company.
Is the a way of doing this?
Brian