Extracting names from either side of a comma

[DPChristman]

This is something I thought I knew how to do, but it isn't working.

I have a column in my spreadsheet with names in the Lname,Fname cormat (Example: Smith,John A)

Say this data appears in cell A2. I thought the way to get is was <=RIGHT(A2,(FIND("",A2,1)-1))&" "&LEFT(A2,(FIND(",",A2,1)-1)) > or something like that.



However, this is not working, Does anyone have a quick way to make this work?

 

[alansidman]

Revised. Missing the comma in the first Find

=RIGHT(A1,(FIND(",",A1,1)-1))&" "&LEFT(A1,(FIND(",",A1,1)-1))

 

[DPChristman]

Revised. Missing the comma in the first Find

=RIGHT(A1,(FIND(",",A1,1)-1))&" "&LEFT(A1,(FIND(",",A1,1)-1))

alansidman - Thank you. Unfortunately, there still seems to be a problem.
If the data field (A2) is “Smith,Candace R”, the formula returns “ndace R Smith:
Additionally, some of the entries are return extra characters.
Example: When the cell shows “Johnstone,John”, the formula returns “tone, John Johnstone”
So, it appears I am missing part of the formula

 

[DanteAmor]

HJow about this options:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:128.32px;" /><col style="width:175.84px;" /><col style="width:103.6px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Smith,John A</td><td >John A Smith</td><td >John A Smith</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Smith,Candace R</td><td >Candace R Smith</td><td >Candace R Smith</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Johnstone,John</td><td >John Johnstone</td><td >John Johnstone</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=TRIM(RIGHT(SUBSTITUTE(A2,",", REPT(" ",99)),50)) & " " & TRIM(LEFT(SUBSTITUTE(A2,",", REPT(" ",99)),50))</td></tr><tr><td >C2</td><td >=MID(A2,FIND(",",A2)+1,LEN(A2)) & " " & LEFT(A2,FIND(",",A2)-1)</td></tr></table></td></tr></table>

 

[Tom.Jones]

Try:

=RIGHT(A2,LEN(A2)-FIND(",",A2,1))&" "&LEFT(A2,(FIND(",",A2,1)-1))

 

[Rick Rothstein]

Does this do what you want...

=TRIM(MID(A2&" "&A2,FIND(",",A2)+1,LEN(A2)))

If you will never have a space after the comma, you can remove the TRIM function call...

=MID(A1&" "&A1,FIND(",",A1)+1,LEN(A1))

 

[alansidman]

Here is a Power Query Alternative. Very Quick and Very Simple. Check out using Power Query for Data Manipulations.
https://www.poweredsolutions.co/2015/01/23/power-query/

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitColumnbyComma = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    SplitColumnbySpace = Table.SplitColumn(SplitColumnbyComma, "Column1.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    RemoveNullValue = Table.ReplaceValue(SplitColumnbySpace,null,"",Replacer.ReplaceValue,{"Column1.2.2"}),
    Concatenate = Table.AddColumn(RemoveNullValue, "Custom", each [Column1.2.1] & " " &[Column1.2.2] & " " &[Column1.1])
in
    Concatenate

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

Column1.1​

[/td][td]

Column1.2.1​

[/td][td]

Column1.2.2​

[/td][td]

Custom​

[/td][/tr]


[tr][td]

2​

[/td][td]

Smith​

[/td][td]

John​

[/td][td]

A​

[/td][td]

John A Smith​

[/td][/tr]


[tr][td]

3​

[/td][td]

Smith​

[/td][td]

Candace​

[/td][td]

R​

[/td][td]

Candace R Smith​

[/td][/tr]


[tr][td]

4​

[/td][td]

Johnstone​

[/td][td]

John​

[/td][td]

​

[/td][td]

John Johnstone​

[/td][/tr]
[/table]

 

[DPChristman]

HJow about this options:

	A	B	C
Smith,John A	John A Smith	John A Smith
Smith,Candace R	Candace R Smith	Candace R Smith
Johnstone,John	John Johnstone	John Johnstone

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:128.32px;"><col style="width:175.84px;"><col style="width:103.6px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

</tbody>

Cell Formula B2 =TRIM(RIGHT(SUBSTITUTE(A2,",", REPT(" ",99)),50)) & " " & TRIM(LEFT(SUBSTITUTE(A2,",", REPT(" ",99)),50)) C2 =MID(A2,FIND(",",A2)+1,LEN(A2)) & " " & LEFT(A2,FIND(",",A2)-1) <tbody> </tbody>

<tbody>
</tbody>

Thanks this works great

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.