Extracting Name from Cell with Date and Name Data

Jessrozy11

New Member
Joined
Mar 1, 2019
Messages
6
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

tuckercs

New Member
Joined
Mar 1, 2019
Messages
3
=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

Well-known Member
Joined
Oct 24, 2015
Messages
6,727
Done with PowerQuery aka Get&Transform

rawraw.2raw.1
01/27/2019-Gxxxxxxx, TxxxxxTxxxxxGxxxxxxx
01/26/2019-Kxxxxxxx, ExxxExxxKxxxxxxx
01/02/2019-Mxxxx, AxxxxxxxAxxxxxxxMxxxx
01/27/2019-Gxxxxxxx, TxxxxxTxxxxxGxxxxxxx

Code:
[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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

To reverse the first/last name at the same time, try this:
Code:
=MID(A1,FIND(", ",A1)+2,LEN(A1))&" "&MID(A1,FIND("-",A1)+1,FIND(",",A1)-FIND("-",A1)-1)
 

Jessrozy11

New Member
Joined
Mar 1, 2019
Messages
6

ADVERTISEMENT

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!
 

Jessrozy11

New Member
Joined
Mar 1, 2019
Messages
6

ADVERTISEMENT

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

Jessrozy11

New Member
Joined
Mar 1, 2019
Messages
6
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows
You can apply the TRIM function to my code to get rid of any extra spaces, i.e.
Code:
=[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

New Member
Joined
Mar 1, 2019
Messages
6
Adding the TRIM function didn't work but I think I'll be ok. This was great assistance! Thank you all!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,818
Messages
5,525,077
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top