Regex

[Phermes1]

Regex is still nearly a total mystery to me, but I think this is something it would be able to do. Any help is appreciated.

I'm pulling data off of a report. Each line that I need starts with a zero and a space, and then the description and amount. Like this

0 ABC Transactions              50.00

0 BCDEF Transactions       100.00

I want to find each line and grab the first word after the zero, which will be varying lengths. So along with the amount, my output would be:

ABC         50.00

BCDEF   100.00

I know how to grab the entire description no problem, but I'm hoping to just get the vendor name and not 'transactions', just for the sake of it looking cleaner. I know I could grab it all, then drop a formula in later and just remove the stuff I don't want pretty easily. I'm thinking Regex might be a better solution though.

Thanks for any help!

-Paul

[atcodedog05]

Hi @Phermes1 

Here is how i would do it in regex tool parse mode. Capture the first word after "0 " and number after transaction.

Hope this helps 🙂

[atcodedog05]

Hi @Phermes1 

If your vendor column which has value "ABC" always has a string without spaces. Then you can use data cleanse tool to remove duplicate spaces and use text to column (separator : space) to split them into columns without regex 🙂

Hope this helps 🙂

[GJNom]

Hey Paul,

This might help you. Let me know if you face any problem.

[GJNom]

@Phermes1 , If you want both vendor name and amount in one cell then you can use the replace method of regex as shown in the image below.