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
=REGEXREPLACE(A1, "&([^;]+)\b([^;]+)", "$2;$1$2")