Multiple delimiters but also multiple account numbers

Hi all,

I have this problem/challenge at work that I need to solve. We were given by clients a trial balance that contains account number + delimiter + description however for some accounts it contains main account number +delimiter+ description + sub account and in other cases it contains two sub accounts.

This makes very challenging to focus on 1 delimiter.

I have in my database the account number and description but I need to match those with the new amounts but those new amounts are tied to the account number + delimiter+description, etc...

In an ideal world I would like to use a lookup type that looks into the description, finds the account number and then gets the amount, however I am not sure how to do this or what would be the best approach. I tried parse, but ended up with 7 extra columns.

As you can see in the example below I want to get the amounts in blue but the position is not consistent.

I will appreciate any help and suggestions.

Thank you,

Parse

Accepted answers

CarliE

Hi @sergiogarciagt

Sorry for the late reply!

Attached is the workbook. There were 2 un-matching so I did a check and then parsed out the ones that didn't match separately. All is checked out and working!

Please mark as a solution if this solution works for you 🙂 have a nice labor day weekend!

Multiple delimiters but also multiple account numbers.yxmd

All comments

messi007

@sergiogarciagt,

Could you share a sample data with what you expect to have?

It will help us to give you the solution.

Regards

CarliE

Hi @sergiogarciagt

If you're looking to capture what was highlighted you can use the parse tool to parse out that data with this regex statement \d+\w+)\s•\s\w.+

If this was helpful, please mark it as a solution so other users can benefit!

Thanks 🙂

CarliE

I missed the 2 bottom records

3007300R • Scholarship Funds-Temp Restrict:307330R •Scholarship
3007300R • Scholarship Funds-Temp Restrict:307330R • Scholarship

.. this regex statement would be better and work on all. - \d+\w+)\s•.+

sergiogarciagt

Thank you! this is exactly what I want to achieve. For some reason when input the formula it is giving me all null values. I am attaching the print screen and also a sample of the data and the desired result.

Parse Workflow.PNG

Sample Data for parsing.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703