Separate Multiple Invoice numbers from Comments

[ArijitRoy]

Hello Friends,

I need help to separate Invoice numbers from Comments column to separate tabs.

In some comments, there is only one invoice number and in some multiple. All the invoice numbers contain 15 digits.

How to separate then into separate columns or what best you can give.

Kindly check the attached sample file.

[MilindG]

@ArijitRoy See if this works for you

[ArijitRoy]

Hi @MilindG,

Great work done but there are some Invoice numbers if you check in the Filter with 0. I also need those.

[Rana_Kareem]

Hi @ArijitRoy ..

Also, you can use RegEx Tool with Tokenize Method.

 

[mceleavey · Accepted solution]

Hi @ArijitRoy ,

You can use Regex to tokenise the records to rows using the following pattern:

(\d{15}+)

Be sure to include a recordID before this so you can pivot the data back.

I'm using the to rows method as if you are using the columns method you need to specify the number of columns, and this would need to be known ahead of time, or you could go really high then drop null columns. Both good. However, this method will always take into account the correct number of columns.

You then create a multi-row formula giving the invoice number instance per recordID, then pivots the data around the ID:

This gives the following results:

Workflow attached. I hope this helps.

M.

[MilindG]

@ArijitRoy Rana_Kareem has it right. I have altered the workflow and attached it for you.