Separating text when someone inputted the middle initial with the last name.

[MrBurn5]

Help... I've done something like this before where I had a names entered in to the same cell, but they always had spaces between the first and last or first, middle, last.

This time around I'm looking at 15,000 entries where it's a capitalized middle initial immediately followed by the capitalized last name... The only caveat is that not all cells have the middle initial. See table below for an example.

The best way to do this would be to ask it IF second character is UPPERCASE, then put the First letter in Cell C, and all the rest of the text in Cell D.

I've been struggling with this for a few hours and finally gave up. I know it should be possible but I'm getting increasingly frustrated.

Unknown 64 bit


[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]Combined[/TD]
[TD][/TD]
[TD]Middle initial[/TD]
[TD]last name[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]AJenkins[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Jenkins[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]Jones[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]SJones[/TD]
[TD][/TD]
[TD]S[/TD]
[TD]Jones[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]MWeaver[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]Weaver[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]DWatkins[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]Watkins[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD]Williams[/TD]
[TD][/TD]
[TD][/TD]
[TD]Williams[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

This should help:

Book1
	A	B	C	D
1	Combined		Middle initial	last name
2	AJenkins		A	Jenkins
3	Jones			Jones
4	SJones		S	Jones
5	MWeaver		M	Weaver
6	DWatkins		D	Watkins
7	Williams			Williams
Sheet42

Cell Formulas
Range	Formula
C2	=IF(AND(CODE(MID(A2,2,1))>63,CODE(MID(A2,2,1))<91),LEFT(A2,1),"")
D2	=IF(AND(CODE(MID(A2,2,1))>63,CODE(MID(A2,2,1))<91),MID(A2,2,255),A2)

Formulas copied down.

 

[MrBurn5]

Hi,

This should help:

Formulas copied down.

Thank you! When it comes to text problems my skills are sorely lacking. I need to be better about learning CODE and MID since I've been it used a lot for my other problems.

 

[jtakw]

Oops, made a mistake in the formula, 63 should be 64:

Book1
	A	B	C	D
1	Combined		Middle initial	last name
2	AJenkins		A	Jenkins
3	Jones			Jones
4	SJones		S	Jones
5	MWeaver		M	Weaver
6	DWatkins		D	Watkins
7	Williams			Williams
Sheet42

Cell Formulas
Range	Formula
C2	=IF(AND(CODE(MID(A2,2,1))>64,CODE(MID(A2,2,1))<91),LEFT(A2,1),"")
D2	=IF(AND(CODE(MID(A2,2,1))>64,CODE(MID(A2,2,1))<91),MID(A2,2,255),A2)

 

[jtakw]

Thank you! When it comes to text problems my skills are sorely lacking. I need to be better about learning CODE and MID since I've been it used a lot for my other problems.

You're welcome, don't forget to change the 63 to 64. (although the formulas will still work without the change unless you have "@" as the second character)

 

[Rick Rothstein]

Here are another (shorter) set of formulas that you can consider...

C2: =LEFT(A2,EXACT(MID(A2,2,1),UPPER(MID(A2,2,1))))

D2: =MID(A2,1+LEN(C2),LEN(A2))

 

[MrBurn5]

You guys are awesome. Thank you so much.