That worked perfectly... but then the other logic I already had created problems. :-(
The data is pretty odd, so trying to accommodate all its oddness is a challenge. A sample, columm1 is how is looks and column 2 is what I'm trying to achieve.
Its quite a challenge so any thoughts greatly appreciated. :-)
ZAUNER/BRENDANMR1 ZAUNER/BRENDAN ZAUNER/BRENDANMR ZAUNER/BRENDAN SOMERVILLE/ANDREW MR1 SOMERVILLE/ANDREW SOMERVILLE/ANDREW MR SOMERVILLE/ANDREW GRUBB/CAMILLE NICOLA OWTON MS GRUBB/CAMILLENICOLAOWTON ROBERTSON/BILLIE MS ROBERTSON/BILLIE ROBERTSON/BILLIE MISS1 ROBERTSON/BILLIE ROBERTSON/BILLIE MISS ROBERTSON/BILLIE ROBERTSON/BILLIE MS1 ROBERTSON/BILLIE /THUNDER/SYDNEYMS THUNDER/SYDNEYMS /TIGERSHBASYD TIGERSHBASYD 2018 GOGOL BORDELLO TOUR 2018GOGOLBORDELLOTOUR 5 SECONDS OF SUMMER TOUR 2018 5SECONDSOFSUMMERTOUR2018 A B DE CARVALHO ALMEIDA/JOSE FREDERICO MR ABDECARVALHOALMEIDA/JOSEFREDERICO ABBONIZIO/EMILIO MR ABBONIZIO/EMILIO ABBONIZIO/EMILIO MR3 ABBONIZIO/EMILIO ABBONIZIO/EMILIO MR4 ABBONIZIO/EMILIO AGNEW/MELISSA DR AGNEW/MELISSA AGNEW/MELISSA DR10 AGNEW/MELISSA ALBRECHTSEN/JANET MS ALBRECHTSEN/JANET ALBRECHTSEN/JANET1 ALBRECHTSEN/JANET ALBRECHTSEN/JANET2 ALBRECHTSEN/JANET BORECKI CARMONA/CINDY MISS BORECKICARMONA/CINDY BORECKICARMONA/CINDYMISS BORECKICARMONA/CINDY LOUEY GUNG/ROZALIND JESSIE MS LOUEYGUNG/ROZALINDJESSIE LOUEYGUNG/ROZALIND JESSIE MS LOUEYGUNG/ROZALINDJESSIE O'CONNOR/LUCAS PAUL REGINALD M OCONNOR/LUCASPAULREGINALD OCONNOR/LUCAS PAUL REGINALD MR OCONNOR/LUCASPAULREGINALD VAN RAALTE/STEPHEN MR VANRAALTE/STEPHEN VAN RAALTE/STEPHEN MR1 VANRAALTE/STEPHEN VAN RAAY/REBECCA MS VANRAAY/REBECCA VAN RAAY/REBECCA MS1 VANRAAY/REBECCA
I deleted my last as it was in error. T'was me . The order of what happens in massaging data is critical and even I didn't catch how I'd written that calc and how it would play out. So, figured it out mostly for you, leaving only a couple which are a challenge. The .tflx is attached to this post for you so you can see the steps. Your output will be the first column...Remove Titles.
I rearranged the order of this calculation so that anything with a 'ABCn' was addressed FIRST, then anything with just 'ABC' would be last and that resolved the first issue.
The other steps removes the spaces but keeps the numbers. So there's something I think you'll need to address as to whether you'll keep numbers as some of the customer names are tour years/dates and simply removing the numbers would also remove those elements.
On some, because Find or Contains and Replace would globally change all '/' and because you're using that to separate last name/first names, you'll need to simply step in and edit those values where someone entered a preceding '/'. Unless someone else has an idea on how to extract and replace using a nested REGEX calc. I haven't seen one though.
Hope that helps with your efforts! Don
TravelBookingFlow.tflx 11.3 KB