Extracting Name from Cell with Date and Name Data

[Jessrozy11]

Hi,

I have a cell that has date and name (last name, first name):

01/27/2019-Gxxxxxxx, Txxxxx

01/26/2019-Kxxxxxxx, Exxx

01/02/2019-Mxxxx, Axxxxxxx

01/27/2019-Gxxxxxxx, Txxxxx

<tbody>
</tbody>

I need to remove the date and only see the name and preferably in the opposite order. Right now it is showing last name, first name. Prefer first name last name.

I'd like to see data as:
Txxxxx Gxxxxxxx
Exxx Kxxxxxxx
Axxxxxxx Mxxxx
Txxxxx Gxxxxxxx

I tried =MID($A2, 12, 30) but I’m worried the date format may come through as 01/2/2019 for the single digit days. I also wasn’t able to figure out how to switch the name order either. Is there an easier way or different formula I can use?

Greatly appreciated!
Jessica

 

[tuckercs]

=RIGHT(A1,LEN(A1)-FIND("-",A1,1)) will give you the same results that you received with your formula without having to worry about the date.

 

[sandy666]

Done with PowerQuery aka Get&Transform

raw		raw.2	raw.1
01/27/2019-Gxxxxxxx, Txxxxx		Txxxxx	Gxxxxxxx
01/26/2019-Kxxxxxxx, Exxx		Exxx	Kxxxxxxx
01/02/2019-Mxxxx, Axxxxxxx		Axxxxxxx	Mxxxx
01/27/2019-Gxxxxxxx, Txxxxx		Txxxxx	Gxxxxxxx

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"raw", each Text.AfterDelimiter(_, "-"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "raw", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"raw.1", "raw.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter",{"raw.2", "raw.1"})
in
    #"Reordered Columns"[/SIZE]

 

[Joe4]

Welcome to the Board!

To reverse the first/last name at the same time, try this:

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

 

[Jessrozy11]

After using this code, I realized there are additional / unnecessary spaces after the first name in the original cell. Any recommendation on how to eliminate that space?

Before code: 01/27/2019-Gxxxxxxx, Txxxxx
After code: Txxxxx Gxxxxxxx

Thank you!

 

[sandy666]

which code?

 

[Jessrozy11]

I don't think it appeared correctly. There are 6 spaces between first name last name.

 

[Jessrozy11]

Sorry! I'm new to this. I used code:
=MID(A1,FIND(", ",A1)+2,LEN(A1))&" "&MID(A1,FIND("-",A1)+1,FIND(",",A1)-FIND("-",A1)-1)

 

[Joe4]

You can apply the TRIM function to my code to get rid of any extra spaces, i.e.

=[COLOR=#ff0000]TRIM[/COLOR](MID(A1,FIND(", ",A1)+2,LEN(A1))) & " " & [COLOR=#ff0000]TRIM[/COLOR](MID(A1,FIND("-",A1)+1,FIND(",",A1)-FIND("-",A1)-1))

 

[Jessrozy11]

Adding the TRIM function didn't work but I think I'll be ok. This was great assistance! Thank you all!