Extract character from various string lengths

[Jockeyp]

Hello,

I have data that is a mix of 3 - 5 characters long contained within a cell. I want to extract each character into a new cell, and to use formulas to do it. I can use RIGHT and MID to get 3 out of the 5 out working from the right (see layout below0. The problem occurs when a cell only has 3 characters in it. Using MID just repeats the last character where I want it to be left blank.
The table below shows the output that I want to get to.

15214	1	5	2	1	4
5T44		5	T	4	4
TT2			T	T	2
61344	6	1	3	4	4

My data has a miix of numbers and letters in the first cell. I have 15000 rows of data so looking for a formula to solve this for each column and leaves the blanks for less than 5 characters.

Thanks in advance

Neil

 

[Joe4]

If they are all 3-5 characters, and you want the blanks in the beginning, and not at the end (like in your example), then use the following formulas for a value in cell A2:

Character 1:

=IF(LEN(A2)=5,LEFT(A2,1),"")

Character 2:

=IF(LEN(A2)>=4,LEFT(RIGHT(A2,4),1),"")

Character 3:

=LEFT(RIGHT(A2,3),1)

Character 4:

=LEFT(RIGHT(A2,2),1)

Character 5:

=RIGHT(A2,1)

 

[Joe4]

Here is another way to do it that is just one formula.

Assuming your value is in cell A2, and you want each of the 5 characters to appear in columns B-F,
enter this formula in cell B2 and copy across to F2:

=MID(REPT(" ",5-LEN($A2))&$A2,COLUMN()-1,1)

and then copy down for all rows.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F
1
2	15214	1	5	2	1	4
3	5T44		5	T	4	4
4	TT2			T	T	2
5	61344	6	1	3	4	4
Main

Cell Formulas
Range	Formula
B2:F5	B2	=IFERROR(MID($A2,LEN($A2)-5+COLUMNS($B2:B2),1),"")

 

[Jockeyp]

Thanks Joe,

Got it working with your first solution. This forum is always really helpful.

Much appreciated.

Neil

 

[Joe4]

Excellent!
Glad we were able to help!