How to Modify Cell Data That Occurs in Multiple Columns?

Hello,

I am extracting data from a very large excel file. After rearranging names which are in "[lastname], [firstname]" formatting, an example of the extraction looks like this:

Name1	Age	Manager1	Manager2
Jamie Jones	40	Anna Belle	Mary-Ann Lou
Roger Frank	20	Mary-Ann Lou	Anthony Bjornik

Out of all of the names, I just want to modify "Mary-Ann Lou" such that her name becomes "Mary Lou", removing the hyphen and the name after it. I have tried using the replace tool but haven't been able to do it successfully. Find and replace only looks at one certain column to find and replace, but this name appears in several. I could make one for every column but that seems inefficient. How shall I go about this?

Help

Accepted answers

Matthew

just add a new formula for each column you want to do this to

yes, formulas can write over existing columns instead of creating new columns

All comments

Matthew

a simple regex replace should work

REGEX_Replace([Field1], '-\w*', '')

shaheer

If I want this to work for multiple columns, would I just add them into the REGEX formula? For example,

REGEX_Replace([Field1],[Field2],[Field3] '-\w*', ''). This name appears in multiple columns. Also, I wish to replace the name, not create a new column for it.

Matthew

just add a new formula for each column you want to do this to

yes, formulas can write over existing columns instead of creating new columns

shaheer

Didn't even notice that, thank you so much!

MilindG

@shaheer You can also use Multi-Fiels Formula tool if you need several columns needs to be changed.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335