Person Name Spilt to Rows

DelData

Can you help…I have five rows of sample data where I need to spilt different people’s names into sperate rows.

I get the names in a format that is not great and splitting them into separate rows is not easy.

As a guide customer names are in the following format…

Surname, FirstName: Region Country

...and customers are spilt by a comma, but commas are used to also spilt the surname and forename.

In the excel attached I show how I get the data in the before worksheet and show what I want in the After spreadsheet.

This fix needs a clever data manipulation trick that I do not have. Can you help?

Name Workflow.yxzp

Name Data.xlsx

JosephSerpis

Hi @DelData you can regex in this scenario to spilt the data.

Full Expression in Regex Tool
[^,]+?,\s*[^:]+?\s*:\s*[^,]+

Explanation
[^,]+? → last name (up to first comma)
,\s* → the comma after last name (with optional spaces)
[^:]+?\s* → first name (up to colon, spaces allowed)
:\s* → the colon (with optional spaces)
[^,]+ → value/location (up to the next separator comma)