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
Solved! Go to Solution.
Can you kindly provide a bigger dataset in Excel format?
Hi @jwlam ,
Assuming your data does not have middle name, here is a potential solution.
I hope it helps.
Workflow
Output