Help with regex sintax (replace string)

[mauricio]

Hi!

I'm trying with no success to replace te string "john    wick" for the string "john wick"

The difference between the two strings is that one have a tab and the other have a space between the words.

This string are in a csv file separated by tabs, and that misplaced tab in the record causes a error "Too Many Fields in record"

I tried to solve it using:

Replace([Field], "john    wick", "john wick")

but the formula does not find the string with the embeded tab

Maybe this can be acomplished using REGEX_Replace, but I'm not shure about the sintax.

Other option wold be to ignore delimiters between "john" and "wick" when parsing to columns, but I don't know the sintax either

[ArtApa]

Hi @mauricio - I suggest you to try a Data Cleansing tool first:

[mauricio]

Hi @ArtApa 

Unfortunately I can't because it will remove all other tabs that are used to parse the data into columns.

I need to surgically remove only the tabs that are between "John" and "Wick"

[apathetichell]

if you are looking to do a regex replace for a tab (and you are sure it's a tab)  it's something like:

regex_replace([field1],"(\w+)\t(\w+)","$1 $2") or you can do regex_replace([field1],"(\w+)\t(\w+)","$2, $1") and "Wick, John"

[ArtApa · Accepted solution]

Sure. Then use this: REGEX_Replace([Col1], "john\twick", "John Wick")

[mauricio]

@ArtApa 

That worked perfectly thanks.