How to extract words from cell.

[bubbataks]

Hello,

I have cells that contain text and people's full names. An example of the verbiage in cell A1 is:

"Total For Smith, John C"

I'd like to just have "John" display in B1.

There are also some names that do not have a middle initial.

Thank you so much!

 

[alansidman]

Suggest you show several variations of your data. Your one variation can be resolved as follows

=MID(A1,FIND(",",A1)+1,SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),4))-FIND(",",A1))

 

[bubbataks]

Thank you, that worked for the names with middle initials. Wondering if there is a way to make it so that it can also work for both names that include a middle initial and ones that don't.

Here is an example of the names:


Total For Smith, John C
Total For Holmes, Sherlock
Total For Carter, James
Total For Jones, Mark B

Thank you,

 

[alansidman]

If you are willing to use Power Query/Get and Transform which is available on versions of Excel 2010 and later then this Mcode will work

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Column1.4"})
in
    #"Removed Other Columns"

Steps:

Import your table/range to Power Query
Split your column based upon a space delimeter
Delete all columns except the first name
Close and Load to your spreadsheet.

 

[DanteAmor]

always starts with "Total For"?
Then:
=mid(A1,10, len(A1)-10)