Parsing with regex

Hi -

I want to get rid of all of the numbers and spaces after these labels - I tried removing numbers with data cleansing tool and was left with the commas in the numbers, and I can't remove punctuation because I need the dashes in the words:

SALES 2,015 341,912.81 60,758 9,911,261.62

SALES CLEARING ADJUSTMENTS 0 0.00 56 22,213.72-

FINANCE CHARGE DEBIT ADJ-CLEARING 0 0.00 0 0.00

Thanks,

Jen

Regex

Preparation

Accepted answers

atcodedog05

Hi @jenner85

I really wanted to used regex on this but there is a issue for some reason. Here is a workaround. Basically i am finding double spaces and replaicing them ! by using them as reference i am keeping the first column.

I hope this is what you were looking for

Hope this helps 🙂 Feel to ask if you have any questions

clean.yxmd

leon2020

Not used Regex, but I would use a formula tool here... first looking for the first double-space in the string, then using the LEFT function to read that number of characters.

jenner85

thanks everyone! This worked for me also:

^(\D+) \s\s

All comments

atcodedog05

Hi @jenner85

I hope this is what you were looking for

Hope this helps 🙂 Feel to ask if you have any questions

clean.yxmd

leon2020

Not used Regex, but I would use a formula tool here... first looking for the first double-space in the string, then using the LEFT function to read that number of characters.

atcodedog05

Interesting approach @leon2020 🙂

jenner85

thanks everyone! This worked for me also:

^(\D+) \s\s

atcodedog05

Happy to help 🙂 @jenner85

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

estherb47

@jenner85

Great work in finding a solution on your own. I was just writing that suggestion myself - am a big fan of \D in regex to find non-numbers in a group. Then the double space defines where you cut off the information.

Cheers!

Esther

djaipras

That looks like it targets the character strings at the start of the string instead of removing digits and punctuation.

If wanting to remove digits and punctuation (assuming that the string might not always start with a non-digit), this could also work using the formula tool: