Separate data into 3 columns

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.

Book1.xlsx

Custom Tools

Accepted answers

binu_acs

@ArijitRoy updated the workfow

binu_acs

@ArijitRoy updated the workflow. Can you check now?

Split_Address_011023.yxmd

All comments

binu_acs

@ArijitRoy Oneway of doing this

Split_Address_011023.yxmd

ArijitRoy

Please read my query properly. Row 3 and 13 is incorrect.

binu_acs

@ArijitRoy updated the workfow

binu_acs

@ArijitRoy How you are deriving Addr1 for the below highlighted rows

ArijitRoy

Sorry, I forgot to remove those. Can you please describe in detail, how you have used the first formula (Address).

It worked. Thank you very much 😊

binu_acs

@ArijitRoy The Address formula checks for the length and if it is less than 14 and the first letter of the address starts with a capital letter then just append some space at the end of the address, if the length is less than 14 and the first letter of the address starts with a digit (which means the first 4 letters are missing) then add 4 spaces in the beginning of the Address field. Once the address field is updated then the parsing is easy

MichelleMitchellLutz

Hi @ArijitRoy,

Please see attached. I built in some logic to determine the solution. Please mark this as the solution if this works for you.

Separate data into 3 columns - Solution MML.yxmd

ArijitRoy

Hi @MichelleMitchellLutz ,

First letter will not be B every time. So, the formula you have applied will not work.

ArijitRoy

Hi @binu_acs,

I have a question, if I have the the data like B1FVKW2. 7 digits is missing from the center then what will be the formula.

IIF(Length([Address]) <14 AND REGEX_Match([Address], '^\u.*'), PadRight([Address], 14 , ' '), IIF(Length([Address]) <14 AND REGEX_Match([Address], '^\d.*'), ' '+ [Address],[Address]))

What will be added in to this formula?

binu_acs

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

ArijitRoy

Yes

ArijitRoy

First digit always be 0 and total 7 digits.

binu_acs

@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