Complex splitting a cell into various columns

[ahs004]

Hi All

I have a column which has 11,12,13,14 and 15 character reference numbers which I need to seperate it into several columns. This would have been easy if the lenghts of characters were the same using LEFT, MID and RIGHT functions but I am struggling.

Here is a sample data and what I want to achieve.

CS1314LONDW002

I want to seperate the above into following

A B C D E F (Column headers)
CS 1314 LON DW 00 2

Ideally I want to seperate it like above but becasue having different lenghts its making it beyond my expertise.
The good thing is that the data is uniformed so the each reference number is identical so we can count from left for columns A and B, count from right for columsn F, E and D. C is the column that makes the difference in the character length so the difference should go in to column C.

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Jakobshavn]

We need more samples of your data to see why you can't use a simple MID() formula.

[ahs004]

Attached is a sample data.

Thank you

[XOR LX]

Hi,

The third entry in your list (CS1314LONDW023) does not match the format of the others as it does not contain the "00" string, only a single "0" - is this an error on your part?

Regards

[XOR LX]

Also,

Your desired splits for the first three entries (CS1314LONDW002, CS1314LONDW003 and CS1314LONDW023) repeat the "D" in the 3rd and 4th split, i.e. "LOND" & "DW". Can you exlain why this is the case?

Regards

[ahs004]

you are right, thats an error, the last column should be a single digit and one next to last 02.

Hi,

The third entry in your list (CS1314LONDW023) does not match the format of the others as it does not contain the "00" string, only a single "0" - is this an error on your part?

Regards

[ahs004]

"LOND" Stands for Londond and "DW" stands for a work order.

I have over 10k with 100 different denominations. this was just a sample. data is unique except the region.

Also,


Your desired splits for the first three entries (CS1314LONDW002, CS1314LONDW003 and CS1314LONDW023) repeat the "D" in the 3rd and 4th split, i.e. "LOND" & "DW". Can you exlain why this is the case?

Regards

[XOR LX]

Ok, but both "LOND" and "DW" are not present in those examples, unless you use the "D" 'twice'.

Regards

[ahs004]

DOH!, Sorry I am loosing the plot.

That is my mistake, it is LON and DW.

[XOR LX]

Hi,

Cell B2 and copy down: =LEFT(A2,2)

Cell C2 and copy down: =--MID(A2,3,4) [if you want numerical entries to be returned; if not, =MID(A2,3,4)]

Cell D2 and copy down: =MID(A2,FIND(C2,A2)+4,FIND(E2,A2)-(FIND(C2,A2)+4))

Cell E2 and copy down: =MID(A2,FIND(F2,A2)-2,2)

Cell F2 and copy down: =LEFT(RIGHT(A2,3),2)

Cell G2 and copy down: =--RIGHT(A2,1) [again, =RIGHT(A2,1) if you would prefer the result to be text]

Note that some of these formulas depend on entries in subsequent columns being calculated, so don't expect to necessarily see all the correct results until you have all formulas in up to and including column G.

Regards