Separate data into 3 columns

[ArijitRoy]

Hello Friends,

I need your help. I have attached a sheet. I want 1st column data divided into 3 columns. First 4 into Add1 next 7 digits into Add2 and rest at the end Add3.

B8LC0252044KW2 = Add1 (B8LC) then Add2 (0252044) then rest in Add3 (KW2). Add3 is not fixed with length 3. It can be 3 or 4 or 5.

In some rows you will find data starts with 7 digits, then it should go to Add2. Ex. 0253171

In some rows first 4 and then 7 digits are there but not the last data. Then first 4 digits move to Add1 and 7 digits into Add2 and Add3 will be blank.

[binuacs]

@ArijitRoy one question is your Add2 always starting with a digit?

[ArijitRoy]

Yes

[ArijitRoy]

First digit always be 0 and total 7 digits.

[binuacs]

@ArijitRoy I updated the address formula to check whether the position of Add2 starts with 0, if not take the first 4 Chars and concatenate with 7 spaces and the last part of the address

[ArijitRoy]

Can you please delete 7 digits from the 3rd row data and test. In my system, B is missing from the 3rd from the last Add3.

[binuacs]

@ArijitRoy 

deleted the 7 digits from 3rd row 

[ArijitRoy]

Actual data on the 3rd row is B0LNBDT but B is missing in the Add3 result = B0LN        DT

[ArijitRoy]

[binuacs · Accepted solution]

@ArijitRoy updated the workflow. Can you check now?