This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am attempting to remove some text from a cell containing sting values. I've had no luck finding an answer so I'll pose it to the community!
I have an Excel input file that has a column containing names that have the potential to contain a [C] at the end and would like to remove it (RegEx replace or whatever works)
The manager names are in the format of First NameMiddle Name (not always included) Last Name[C] (not always included). I've done everything short of a 'text to columns, formula to recognize and replace the [C], then rejoin' as I'm certain that there's a more elegant solution.
If it makes any difference, I already have a RegEx + formula that parses and rejoins the name into my preferred format. It is
RegEx: ^(\w+)\W?((?<=\W).+(?=\W))?\W?(\w+)?$ Marked groups are Mgr First Name Mgr Middle Name Mgr Last Name
Formula: REGEX_Replace([Mgr Last Name]+', '+[Mgr First Name], '^,|,$|,(?=,)', '') Output is 'Manager' in the format of Last Name, First Name
Any thoughts, RegEx warriors? I've included a stripped down version of my workflow to show what I've got so far. Bonus points for anyone who can help with the hyphenated last name staying together as well!
In order to remove the "[C]" from the Worker's Manager column you can simplify the Regex by just using an EndsWith() formula. This will allow you to recognize the "[C]" and then simply trim it out of your string with a substring() formula.