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

Do you often forget where you parked your car?

Discuss in The Lounge

Poll History