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 Oneway of doing this

[ArijitRoy]

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

[binuacs · Accepted solution]

@ArijitRoy updated the workfow

[binuacs]

@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 😊

[binuacs]

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

[ArijitRoy]

Hi @MichelleMitchellLutz ,

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

[ArijitRoy]

Hi @binuacs,

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?