RegEx: Remove Middle Name, Suffix
I have a column of data with names in the following format:
Smith Jr., Brian Joseph
I'm looking to use Tables: Apply formulas to remove any suffix (Jr, Sr, II, IV, etc), and everything after [,][\s]{first-name}. With the above example, I'd like to end up with:
Smith, Brian
I think I'll need something like the below formula, but having difficulty getting it to work properly, and writing the RegEx for the middle name.
columns['Name'] = columns['Name'].replace(/\s(II)/g,'').replace(/\s(III)/g,'').replace(/\s(IV)/g,'').replace(/\s(Jr.)/g,'').replace(/\s(Jr)/g,'').replace(/\s(Sr.)/g,'').replace(/{regex-here}/g,'')
Names are coming from different datasets with different formats, but I'd like to use them to join those datasets. Some cleanup is required for the join.
Answers
-
Hi @Thomas_937381,
Is it possible that for all the data you have, you can split on spaces and then remove everything after the last space?0 -
Here is some example code that should get you close to what you want
var data = columns['Name'].split(','); // Depending on your data, you might need to split in different ways var fullLastName = data[0]; var fullFirstName = data[1]; var lastName = fullLastName.split(' ')[0]; var firstName = fullFirstName.split(' ')[0]; columns['Name'] = lastName + ', ' + firstName
You may need to tweak this depending on your data formats but should get you started
0