Separate numbers from characters

[hawk77EF]

Hi all,

I have a list of numbers listed like:

50J
33J
4D
Etc.

I would like to have them separated in two columns like
50 J
33 J
4 D
Etc.

For the life of me, I could not find a simple way to do it! Please help!

[bebo021999]

With text in A1
In B1:
=MAX(IFERROR(--MID(A1,1,ROW(INDIRECT("1:"&LEN(A1)))),0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
In C1:
=SUBSTITUTE(A1,B1,"")

[AlKey]

To extract numbers enter formula in B1 and copy down

=LOOKUP(10^308,--LEFT(A1,ROW(A$1:A$15)))

then in C1 and copy down

=SUBSTITUTE(A1,B1,"")

v	A	B	C
1	50J	50	J
2	33J	33	J
3	4D	4	D

[Jonmo1]

If each cell has only 1 letter at the end, then try

=LEFT(A1,LEN(A1)-1)+0
and
RIGHT(A1,1)

[sandy666]

You can try:
1. VBA code
2. B1: =regex(A1,"([0-9]{0,})([a-zA-Z]{0,})","$1")
3. C1: =regex(A1,"([0-9]{0,})([a-zA-Z]{0,})","$2")

[hawk77EF]

Solved, thank a lot everybody!