Multiple delimiters but also multiple account numbers

[sergiogarciagt]

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,

[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.

 

[CarliE · Accepted solution]

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!

[sergiogarciagt]

No worries, this is great!

Thank you very much 👍