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.
Solved! Go to Solution.
Please read my query properly. Row 3 and 13 is incorrect.
@ArijitRoy updated the workfow
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 😊
@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
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.
First letter will not be B every time. So, the formula you have applied will not work.
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?