Thread: Spliting name
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre Ron Coderre is offline
external usenet poster
 
Posts: 2,118
Default Spliting name

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