Take word after last space before comma

[Mr Popodopolous]

I have a list where I want to put it to "Last Name, First Name" but if they have multiple words in their last name, I only want the last part of their last name to ensure that I won't have any errors when I'm performing a check as some reports don't put the full last name.

at the moment I have data like this:

van de Graaf, Generator O'the Morning, Topp

<tbody>
</tbody>

Flintstone, Wilma

I want to be able to have it so it will concatenate it to:

Graaf, Generator
Morning, Topp
Flintstone, Wilma

How can I do this, I'm thinking I have to do something where I have
FIND(",",A10)-FIND(",",SUBSTITUTE(A10," ",""))
which counts the number of spaces before the comma, but how do I use the information from that to get the number of characters after that final space

 

[FormR]

Hi, welcome to the board!

Here is one option you could try:

Excel 2013/2016
	A	B
1	van de Graaf, Generator	Graaf, Generator
2	O'the Morning, Topp	Morning, Topp
3	Flintstone, Wilma	Flintstone, Wilma
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(" "&A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),"|",REPT(" ",255)),255))

 

[Mr Popodopolous]

Hi, welcome to the board!

Here is one option you could try:

Excel 2013/2016

	A	B
1	van de Graaf, Generator	Graaf, Generator
2	O'the Morning, Topp	Morning, Topp
3	Flintstone, Wilma	Flintstone, Wilma

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(" "&A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),"|",REPT(" ",255)),255)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hey thanks, that worked perfectly!

 

[Peter_SSs]

Would this also work for you?

<b>Names</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:171px;" /><col style="width:171px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">van de Graaf, Generator</td><td style="font-size:10pt; ">Graaf, Generator</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">O'the Morning, Topp</td><td style="font-size:10pt; ">Morning, Topp</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Flintstone, Wilma</td><td style="font-size:10pt; ">Flintstone, Wilma</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>Spreadsheet Formulas</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 >B1</td><td >=TRIM(RIGHT<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A1," ",REPT<span style=' color:#ff0000; '>(" ",100)</span>)</span>,200)</span>)</td></tr></table></td></tr></table>

 

[Peter_SSs]

.. or in case there could be more than one name after the comma:

<b>Names (2)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:216px;" /><col style="width:178px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">van de Graaf, Generator</td><td style="font-size:10pt; ">Graaf, Generator</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">O'the Morning, Topp To You</td><td style="font-size:10pt; ">Morning, Topp To You</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Flintstone, Wilma Quarry</td><td style="font-size:10pt; ">Flintstone, Wilma Quarry</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>Spreadsheet Formulas</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 >B1</td><td >=TRIM(RIGHT<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(LEFT<span style=' color:#ff0000; '>(A1,FIND<span style=' color:#804000; '>(",",A1)</span>)</span>," ",REPT<span style=' color:#ff0000; '>(" ",100)</span>)</span>,100)</span>)&MID(A1,FIND<span style=' color:008000; '>(",",A1)</span>+1,100)</td></tr></table></td></tr></table>

 

[Mr Popodopolous]

.. or in case there could be more than one name after the comma:

Names (2)

	A	B
1	van de Graaf, Generator	Graaf, Generator
2	O'the Morning, Topp To You	Morning, Topp To You
3	Flintstone, Wilma Quarry	Flintstone, Wilma Quarry

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:216px;"><col style="width:178px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(",",A1))," ",REPT(" ",100)),100))&MID(A1,FIND(",",A1)+1,100) <tbody> </tbody>

<tbody>
</tbody>

Sorry about the late reply, I've been swamped with other facets of work and haven't been able to look at this since my first reply!
Thank you for the replies, but could I ask for a little more help?

I don't quite have my head around the logic for stringing these formulas together, so how would it go if I wanted to just take the first name after the comma?

Would it be too pushy of me to ask if you could break down the logic of the formula for me?

Thank you!

 

[Peter_SSs]

.. so how would it go if I wanted to just take the first name after the comma?

Do you mean like this?

<b>First name after comma</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:198px;" /><col style="width:118px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">van de Graaf, Generator</td><td style="font-size:10pt; ">Generator</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">O'the Morning, Topp To You</td><td style="font-size:10pt; ">Topp</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Flintstone, Wilma Quarry</td><td style="font-size:10pt; ">Wilma</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>Spreadsheet Formulas</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 >B1</td><td >=REPLACE(LEFT<span style=' color:008000; '>(A1,FIND<span style=' color:#0000ff; '>(" ",A1&" ",FIND<span style=' color:#ff0000; '>(",",A1)</span>+2)</span>-1)</span>,1,FIND<span style=' color:008000; '>(",",A1)</span>+1,"")</td></tr></table></td></tr></table>

Would it be too pushy of me to ask if you could break down the logic of the formula for me?

No, but let's wait until we are sure we have the correct formula for what you want.

 

[Mr Popodopolous]

Is there an issue with the forums vba codes? it all seems to just show as code and not the pretty stuff we want to see!

so this formula works best for taking the first word after the comma:
=REPLACE(LEFT(A4,FIND(" ",A4&" ",FIND(",",A4)+2)-1),1,FIND(",",A4)+1,"")

but if that only works for the first word after the formula.
If there are two words after the comma, the formulas above don't seem to be able to select the last word before the comma.

what formula can I use to get excel to find the last instance of space before the comma? because if I could use that, I could use that with a MID statement and use a concatenate to patch those two formulas together, but it just doesn't seem to be google-able as every link that shows up when you search "excel find last occurance of character before delimiter" seems to just show you how to take everything before a delimiter or after it, which isn't what I want.

So with Peter_SSs second example table what I would like to extract from the following name would be:

Names (2)

	A	B
1	van de Graaf, Generator	Graaf, Generator
2	O'the Morning, Topp To You	Morning, Topp
3	Flintstone, Wilma Quarry	Flintstone, Wilma

but we already know how to get the Generator, Topp and Wilma, it's just the Graaf, Morning, and Flintstone that's giving me issues now.

Thank you again!

 

[sandy666]

maybe with PowerQuery

RAW		Result
van de Graaf, Generator		Graaf, Generator
O'the Morning, Topp To You		Morning, Topp
Flintstone, Wilma Quarry		Flintstone, Wilma

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "RAW", Splitter.SplitTextByAnyDelimiter({", "}, QuoteStyle.Csv)),
    After = Table.TransformColumns(Split, {{"RAW.1", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    Before = Table.TransformColumns(After, {{"RAW.2", each Text.BeforeDelimiter(_, " "), type text}}),
    Result = Table.CombineColumns(Before,{"RAW.1", "RAW.2"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Result")
in
    Result

 

[RoryA]

If there's always a space after the comma:

=TRIM(RIGHT(SUBSTITUTE(MID(A1,1,FIND(",",A1)-1)," ",REPT(" ",1000)),1000))&", "&TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(",",A1)+2,LEN(A1))," ",REPT(" ",1000)),1000))