un-join text strings from one text string

September 27, 2010 at 07:38:34
Specs: Windows XP
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".

See More: un-join text strings from one text string

Report •


#1
September 27, 2010 at 08:17:21
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

http://www.skeptic.com/


Report •
Related Solutions


Ask Question