How can I grab the first initial and the entire last name in a field?

[jwlam]

Hi all,

I'm having trouble getting this to work. 

I have about 1000 rows of data. Each row has anywhere from 1-20 names separated by commas. I've already parsed them out into individual columns (titled Faclitator1 through 20).

What I need now is the first initial and their last name. So something like "John Smith" would become "J. Smith". Then I'll need to concatenate them with commas again (J. Smith, A. Lincoln, etc.)

My idea was to use a formula like Left(String, len) for the first initial but I can't seem to get the last name after that. I was trying RegEx too but I'm not great at it so it isn't working out.

Any ideas?

Data

Desired Result

[Qiu]

@jwlam 

Can you kindly provide a bigger dataset in Excel format? 

[Yoshiro_Fujimori · Accepted solution]

Hi @jwlam ,

Assuming your data does not have middle name, here is a potential solution.

I hope it helps.

Workflow

Output