Hi,

I have an allocation cell with 20 characters creating a text string. Each 4 characters is a code. I have 17500 rows of allocations in column A, but I need now to get the codes from each sting into 5 different columns. (column B, C, D, E, F)

Ex;

Allocation in cell A1

00106896RROHRROPRRIM

Is there a formula that separate 4 characters code for each column? I'm looking for the opposite of "concatenate".

The =LEFT(), =RIGHT(), and =MID() functions should get you what you need. In cell A1 = 00106896RROHRROPRRIM

In cell B1 enter the formula: =LEFT(A1,4)

In cell C1 enter the formula: =MID(A1,5,4)

In cell D1 enter the formula: =MID(A1,9,4)

In cell E1 enter the formula: =MID(A1,13,4)

In cell F1 enter the formula: =RIGHT(A1,4)EDIT:

Also be aware that if you change the first four characters into a real number, then you will loose the leading zeros. To maintain the leading zeros, you will have to Custom Format or format them as TEXT.MIKE

Ask Your Question

Weekly Poll