RegEx: Remove Middle Name, Suffix

Options
Thomas_937381
Thomas_937381 Posts: 196
edited February 2020 in Questions

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.

Tagged:

Answers

  • Tom_835318
    Tom_835318 Posts: 9
    Options

    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?

  • Dylan_191182
    Dylan_191182 Posts: 92 admin
    Options

    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

Categories