Extracting data before and after a comma

[RaviWildcat]

Hello Excel Experts - I have a dilly of a pickle for you

Given the following data:

1. I loaned the sunglasses to Partridge,Keith to use on stage
2. I threw the football to Brady,Marcia but it hit her in the nose
3. Greg bought a guitar and now we call him Bravo,Johnny

I'd like to collect the following information

1. Partridge,Keith
2. Brady,Marcia
3. Bravo,Johnny

I've looked at left() and mid() and find() functions and I can figure out how to get the info if the last names or first names all have the same length. Isn't there a way to get the data to the left and to the right of the comma if I know that they are separated by spaces?

Ravi

 

[Aladin Akyurek]

Try...

B2:

=LEFT(A2,FIND(",",A2)-1)

C2:

=REPLACE(A2,1,FIND(",",A2),"")

 

[Sandeep Warrier]

Hi

Using a helper column

Excel Workbook
	A	B	C
1	I loaned the sunglasses to Partridge,Keith to use on stage	Partridge,Keith to use on stage	Partridge,Keith
2	I threw the football to Brady,Marcia but it hit her in the nose	Brady,Marcia but it hit her in the nose	Brady,Marcia
3	Greg bought a guitar and now we call him Bravo,Johnny	Bravo,Johnny	Bravo,Johnny
Sheet2

Excel 2003

Cell Formulas
Range	Formula
B1	=REPLACE(A1,1,FIND(TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND(",",A1),LEN(A1),"")," ",REPT(" ",255)),255)),A1)-1,"")
C1	=LEFT(B1,FIND(" ",B1&" ")-1)

Edit: Added a VBA based solution too<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> ExtractName(sInp<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> oMat<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN><br>    ExtractName = "No Name Found"<br>    <SPAN style="color:#00007F">With</SPAN> CreateObject("VBScript.RegExp")<br>        .Global =<SPAN style="color:#00007F">True</SPAN><br>        .Pattern = "[A-Za-z]+, ?[A-Za-z]+"<br>        <SPAN style="color:#00007F">If</SPAN> .test(sInp)<SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> oMat = .Execute(sInp)<br>            ExtractName = oMat(0)<br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

Excel Workbook
	A	B
1	I loaned the sunglasses to Partridge,Keith to use on stage	Partridge,Keith
2	I threw the football to Brady,Marcia but it hit her in the nose	Brady,Marcia
3	Greg bought a guitar and now we call him Bravo,Johnny	Bravo,Johnny
Sheet2

Excel 2003

Cell Formulas
Range	Formula
B1	=ExtractName(A1)

 

[Aladin Akyurek]

Try...

B2:

=LEFT(A2,FIND(",",A2)-1)

C2:

=REPLACE(A2,1,FIND(",",A2),"")

Ignore... Read your post too fast.

 

[RaviWildcat]

Sandeep your vba function is sensational! Thank you for your help!

Ravi

 

[redwoodenergy]

Ignore... Read your post too fast.

That's okay. Your information didn't go to waste. You answered the question I wanted to ask!

 

[Aladin Akyurek]

That's okay. Your information didn't go to waste. You answered the question I wanted to ask!

Glad to hear that. Thanks for providing feedback.

 

[Rick Rothstein]

Sandeep your vba function is sensational!

Here is a non-RegExp function that you may want to consider...

Function ExtractName(S As String) As String
  Dim Parts() As String
  Parts = Split(S, ",")
  On Error Resume Next
  ExtractName = "No Name Found"
  ExtractName = Mid(Parts(0), InStrRev(Parts(0), " ", Len(Parts(0)) - 1) + 1) & "," & Left(Parts(1), InStr(2, Parts(1) & " ", " ") - 1)
End Function