extracting alpha numeric characters from one cell into 5 individual cells

[gsdanger]

Good Day all,
I have a column of alpha numeric characters (maximum length is 5, minimum is zero or 1)
I want to extract each character into a separate cell, from the right to the left.
First image is what I have...Second image is what I want...
Hopefully someone can assist me with this.

Kind regards...gsdanger

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have Excel 365, try this. The formula in C2 is just copied down that column and the other results will automatically 'spill' to the right.

21 02 11.xlsm
	A	B	C	D	E	F	G
1
2	5	95x62	9	5	x	6	2
3	4	1257		1	2	5	7
4	1	4					4
5	4	325x		3	2	5	x
6	3	x53			x	5	3
7	2	29				2	9
8	5	956x1	9	5	6	x	1
Sep Characters

Cell Formulas
Range	Formula
A2:A8	A2	=LEN(B2)
C2:G8	C2	=IFERROR(MID(B2,SEQUENCE(,5,A2-4),1),"")
Dynamic array formulas.

 

[Rick Rothstein]

Here is another formula that you can use. Place it in cell C2 and copy it across to cell G2, then copy those 5 cellls (C2:G2) down to the bottom of your data (or beyond if you plan to add more data in Column B). I think this formula should work in all versions of Excel from XL2003 and later (it may work in earlier versions too but I have no experience with them). Note that this formula does not use the lengths calculated in Column A so you remove them if you want.

=SUBSTITUTE(MID(REPT(" ",5-LEN($B2))&$B2,COLUMNS($C:C),1)," ","")

 

[jtakw]

Hi,

Here's another formula option, just for fun:

Book3.xlsx
	A	B	C	D	E	F	G
1
2	5	95x62	9	5	x	6	2
3	4	1257		1	2	5	7
4	1	4					4
5	4	325x		3	2	5	x
6	3	x53			x	5	3
7	2	29				2	9
8	5	956x1	9	5	6	x	1
Sheet755

Cell Formulas
Range	Formula
C2:G8	C2	=TRIM(MID(REPT(" ",5-$A2)&$B2,COLUMNS($C2:C2),1))
A2:A8	A2	=LEN(B2)

 

[Rick Rothstein]

Here's another formula option, just for fun:

Why "just for fun"? You posted the same formula I did (although you used Column A and I didn't) except you were smart enough to use the TRIM function instead of my klunky SUBSTITUTE function.

 

[jtakw]

@Rick Rothstein , Sorry Rick, no offense intended whatsoever, I was working on that exact formula half hour before my posting, with many interruptions, when I finally got back, I saw your post, which I agree, is basically the same formula, but I posted it anyway, the "just for fun" part means nothing more than "just a little different"...if that makes sense...

 

[Tom.Jones]

Without helper column A, and data start from column A:
=TRIM(MID(REPT(" ",5-LEN($A2))&$A2,COLUMNS($B2:B2),1))

 

[Peter_SSs]

I don't know whether column A should be regarded as a "helper column" since it was given as part of the "before" information. Of course any formula that used it could easily be changed to include the LEN() function instead.

For all versions here is another slight variation on the theme of not using column A that is shorter again.

21 02 11.xlsm
	B	C	D	E	F	G
1
2	95x62	9	5	x	6	2
3	1257		1	2	5	7
4	4					4
5	325x		3	2	5	x
6	x53			x	5	3
7	29				2	9
8	956x1	9	5	6	x	1
Sep Characters (2)

Cell Formulas
Range	Formula
C2:G8	C2	=TRIM(LEFT(RIGHT(" "&$B2,COLUMNS(C:$G))))

 

[gsdanger]

Good Day all,
Especially....Rick, Peter_SSs, Tom_Jones and jtakw.
Thank you for your prompt response and expert advice and direction.
I have been fiddling with the Left, Right, Mid and Len functions for some time, trying to achieve my goal.
I'm really glad I decided to ask you guys, as I would have been still trying to nut it out myself.
I would have never thought of using the formula types you guys responded with....Thank you to all...
I have used Rick's code and it has worked fabulously. Thanks Rick. I'm sure all the code supplied by you guys would've worked also, I just picked on at random.

Also, I have updated my account details to reflect that I'm using Microsoft Office 2019.
Thanks for the prompt.

Again, thanks to all you guys for assisting me with this.

Kind Regards...gsdanger.

 

[Peter_SSs]

You're welcome. Glad you got something useful. Thanks for the follow-up and for updating your profile details.