Search code examples
arraysexcelstringgoogle-sheetsworksheet-function

Extract multiple first and last names from a string which contains 2 patterns


I have racked my brain trying to work out a formula that (at its worst) takes a string like this in a single cell:

GARY ROBERT WARD; KEITH MALCOLM TERRILL & CAMILLA MARGARET WEDGWOOD SWAN; CAMILLA ELIZABETH SWAN-WARD

To something that would concatenate the full unique names of each person separated by a ";" or any anchor really:

GARY ROBERT WARD; KEITH MALCOLM TERRILL SWAN; CAMILLA MARGARET WEDGWOOD SWAN; CAMILLA ELIZABETH SWAN-WARD

Ive done every text to column, split, mid, search, google sheet, regex method I can find but no dice.

Please help :(

Thanks!

Tiana


Solution

  • =REGEXREPLACE(A1, "&([^;]+)\b([^;]+)", "$2;$1$2")
    

    https://regex101.com/r/i0Ki9y