Parse One field to Two fields based on UpperCase 4

[ABerkowicz]

Hi - In Excel 2007 is there an easy way (or anyway) to take one field and separate into 2 fields when the first upper case letter appears? For example - I have one column with the following SmithJane and would like one column to have Smith and another column to be Jane.

Thanks for your help!

 

[Gavona]

I think that you need a macro to do this. Try reposting in forum707.

In 2003 I would suggest that you first replace capital letters with [space]capital (Edit,Replace). Then use Data,TexttoColumns.
Record a macro doing this for just one capital letter then edit the code to do it for all capital letters (character code 65 to character code 90).

By the way in your example you want it to split at the second capital letter. The part solution above would split at every capital letter and assumes that there are no spaces in the field to start with.

Gavin

 

[AnotherHiggins]

I'm sure there's a way to do this with array formulas to locate the second capital letter (which would be between characters 65-90), but I'd have to spend some time playing around with it....

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ABerkowicz]

Just an update. What I did was created 26 diff columns and used the formula =find($A1,"A",2) and then =find($A1,"B",2) and so on for each 26 letters. This returned the position of the letter.

Then I did a count and the ones that were 1 I was able to use the len and parse out. I am now having a problem though where there were 2 caps for instance like McDonaldJane since the count would be 2 (the D and the J).

Need to now figure out a way to return the first or last value in the 26 columns where count > 1 so that I can systematically use the last value to determine the parsing. Anyone know of a way to get the last value in a range of columns? For example, column 1 had 5, column 6 had 2 and column 7 had 1. I want last through columns 1 - 7 which would come back with 1.

 

[bluedragon2]

Names are hard and you can usually always find exceptions to what you find. Even if you look for the last upper case, what about SmithJT... I would suggest ,if possible, see if you can get parsed data from the source.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Kurupt55]

Assuming that your names are always LastnameFirstname, would there be first names that would have 2 uppercase letters in it? If not then you could use the max formula
=MAX($B1:$AA1)
this would give you the starting point of the first name IF there are no caps in the first name.
but you would run into problems with a name like McDonaldDavid as it would not pick up the second D
To get around this i would use this formula to ensure that you find the last occurence of the "D"
=MAX(IF(ISERROR(FIND("D",A2)),0,FIND("D",A2)),IF(ISERROR(FIND("D",A2,FIND("D",A2)+1)),0,FIND("D",A2,FIND("D",A2)+1)))
Probably overcomplicated but you would only run into issues when there is 2 caps in the first name

Impossible is Nothing

 

[Gavona]

Formulae solution are always interesting and mentally stimulating to find. But, in this case, I still feel that a VBA solution is the answer (but this is the wrong forum for that). Especially if there is lots of data. The solution I outlined works and is quick.

To only split from the last capital letter I would:
1. replace capital letters with [space]capital (Edit,Replace 26 times using a loop For i = 65 to 90... Next).
2. use a helper column to compare the length of the field with spaces and without. If the difference is greater than 1 then remove all but the last space in the string.
With the text in B4
=IF(LEN(B5)-LEN(SUBSTITUTE(B5," ",""))>1,SUBSTITUTE(B5," ","",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-1),B5)
3. Use Data Text to columns



Gavin

 

[SkipVought]

Quite often with a data cleanup effort, I do it in chunks. After analyzing the data I determine what method will affect the greatest population, apply it then go the the next method and so forth, until you get down to the handful that may not meet any of the previous criteria & methods.

If it is not a ONE TIME EVENT, then I look to programming a solution, based on the process I developed above.

ANALYSIS is a key part of the process in order to determine the method(s) necessary to do the cleanup.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

I might change that to:

"ANALYSIS is a key part of the process in order to determine the method(s) necessary to do the cleanup anything.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[SkipVought]

I hate Gerry for trumping me agree!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

If SmithJane is in Cell A1, then enter
=LEFT(A1,SMALL( FIND(CHAR(ROW(INDIRECT("65:90"))),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)
as an array formula in cell B1.

In cell C1, enter
=REPLACE(A1,1,LEN(B1),"")




A man has only two choices: He can be right or he can be happy.

 

[SkipVought]

xlhelp,

I have not yet attained your proficiency with arrays. ==> [purple]*[/purple]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ABerkowicz]

xlhelp - this is great and works for most. Just not something like LewisLisa where the first cap is the same as the second (L and L). But this gets me very far very quickly and I can just clean up the others manually since this is Data Conversion and only one time. Awesome! Thanks for the information.

 

[kwbMitel]

Aberkowicz:

I am trying to train myself in some matters such as these to see what I can come up with as a solution.

I figured out a way to find the first Capital letter counting from the End instead of the beginning.

There is undoubtably a way using Arrays/Index's or whatnot to accomplish this but here is the sledgehammer approach

This will work as long as the Last Name is not longer than 20 Characters (it can be increased but I didn't want to get silly)

Column A contains the text to be analyzed

Row 1 (Column B to U) = Numbers 20 19 18 ..... 1

B2 formula
=IF(ISERROR(CODE(MID($A3,B$1,1))<=90),"",IF(CODE(MID($A3,B$1,1))<=90,CONCATENATE(B$1,","),""))

Copy B2 to cells C2 thru U2

V2 = Formula
=CONCATENATE(B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,L3,M3,N3,O3,P3,Q3,R3,S3,T3,U3)

W2 Formula
=VALUE(LEFT(V3,FIND(",",V3,1)-1))

Resulting Value in Column W is the location of the Last Capital letter in the string and can then be used to separate the data.

Visual
[tt]
A B C D ... U V W

1 20 19 18 ... 1

2 FordHarrison 5,1, 5

3 LuiLucy 4,1, 4

4 VanDammeClaude 9,4,1, 9

5 McGreggorEwan 9,3,1 9
[/tt]

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.