The Inspire Cyber Monday promo has been extended to Dec. 1! This offer is discounted off the Early Bird price and includes 20% off for conference-only passes and 10% off training passes.
alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Separate data into 3 columns

8 - Asteroid

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.

18 REPLIES 18
19 - Altair

@ArijitRoy Oneway of doing this

8 - Asteroid

19 - Altair

@ArijitRoy updated the workfow

19 - Altair

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

8 - Asteroid

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 😊

19 - Altair

@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

9 - Comet

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.

8 - Asteroid

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

8 - Asteroid

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.