Regex Replace

[hi2019]

Hi

I have a Regex formula but I think the punctuation in the column is giving it an extra count - 

is this formula correct?

REGEX_Replace(ReplaceChar([Name Copy], '. ,', '|'), '\|+', '|')

FRHT FIRST CORP. 

it reads it as count 4 but it should be 3

FRHT/FIRST/CORP/   isnt it countig it like this?

I have another one with punctuation but it is reading it correctly 

DID C. REORD count 3 

or could it be because the ending of these column has approx 7-8 spaces after the last letter? It is messy data and  i have a data cleanse tool to remove extra trailing spaces or does it only account for certain amount of spaces?

[SPetrie · Accepted solution]

Not sure I completely follow you are looking for. I think is the final | at the end you are not wanting, correct?

You can try this formula instead.

REGEX_Replace(Trim(REGEX_Replace([Name Copy],"\W"," ")),'\s+','|')

Replace all non-word characters with a space. Trim the result, and then replace all spaces with |. Someone better at Regex than I can probably come up with a more efficient formula.

[CoG]

What do you mean by count? What is your goal with the RegEx?

[hi2019]

Thanks but do you know how many spaces or trailing spaces Data Cleanse tool would remove? is there specific amount? This excel I am pulling has like 7 to 8 spaces at end of each

Example  John (then 8 blank spaces)

[SPetrie]

No limit on how many in the Data Cleanse tool. You just need to specify leading and trailing, duplicate white space, or all white space when you make your tool selections.

[hi2019]

ok just wanted to make sure it was accouting for the spaces! thanks!

[hi2019]

So I dont think its reading the spaces correctly??

Nick J Span would be a 2 count correct? 

Mine is reading it as 3 count?

Could it be reading a space at the end even if I have a Data cleanse tool - remove trailing space?

[SPetrie]

Can you share a picture of the result? Im not sure what you mean by count and without seeing what you are describing its hard to say. 

If I run that through the regex suggested earlier, it looks like the expected output. Data cleanse should do the same as the trim and as long as you dont have a non-breaking space or some other invisible character at the end, it should also clear it out. 

[CPacheco89]

Hello everyone,

I am having trouble writing a RegEx replace formula to dynamically replace " ? ", question marks, back to apostrophes " ' " that unfortunately get changed after the data is ingested. I'm still relatively new and believe that this is the proper route to take, but if there are any other suggestions, they are welcome and greatly appreciated. 

 

[SPetrie]

@CPacheco89 This should really be a new topic on its own instead of adding on to an existing issue. It will get more eyes on it that way as well.

To answer the question, I dont think you need regex to do this. A regular replace formula should work.

replace([text],"?","'")

If you really want to use regex, this formula should do the same thing.

REGEX_Replace([text],"\?","'")