Regex Replace

hi2019

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?

Reporting

Accepted answers

SPetrie

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.

All comments

SPetrie

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.

Test Text.PNG

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],"\?","'")

CPacheco89

Thank you very much for this and for providing a simpler option! I'll try it out and provide feedback. I initially thought the RegEx tool would be a good option since I didn't want to remove all the question marks in the texts, but only the ones that should be apostrophes. I appreciate you taking the time to share your knowledge with me.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703