Removing suffixes from the name

[Idyllic_Data_Geek]

I have a requirement to remove the suffix from the name field. For example the values in the field can contain suffixes such as JR, SR, II, III, IV

current value                                 New Value
HENRY ANDERSON IV                HENRY ANDERSON
MACK STOVER III                        MACK STOVER
JOHN JOSEPH LAWLER III         JOHN JOSEPH LAWLER
JEFFREY D KIRKLAND JR         JEFFREY D KIRKLAND

The suffix will always be in the end trailing a space. What calc can I leverage to accomplish this? Thanks for your help in advance.

[atcodedog05 · Accepted solution]

Hi @Idyllic_Data_Geek 

Here is how you can do it.

Workflow:

Hope this helps : )

[Qiu]

@Idyllic_Data_Geek 
I think @atcodedog05 provided a good one! 😁

[amruthas2 · Accepted solution]

@Idyllic_Data_Geek 

You could directly use a formula for the expected output

Left([Names],FindString(
REGEX_Replace([Names], "(.*) (.*)", '$1*$2')
,"*"))

[Idyllic_Data_Geek]

@atcodedog05 the small issue with find and replace approach is that it is replacing 'I' anywhere in the string. Where I only want to remove it when it is (space)I,

(space)II,

(space)III,

(space)IV,

(space)JR,

(space)SR