Segregation of alphabets and numbers

[Sandie1712]

Hello,

Could you pl help me separate the numbers and alphabets in different columns without the help of VBA

[FONT=&quot]101ASD234FGHJ 2367DFR45GTYDFG 653RTYUHJKKL412 24RTYUIO3123O

Thanks!!

Sandie[/FONT]

 

[Sandie1712]

Hello data is in four cells of one column

101ASD234FGHJ


2367DFR45GTYDFG


653RTYUHJKKL412


24RTYUIO3123O

 

[AlKey]

Use this array formula to get numbers
**Must be entered with Ctrl+Shift+Enter key combination.

[B]=NPV(-0.9,,IFERROR((MID(A1,256-COLUMN(A:IV),1)%),""))[/B]

then this one to get TEXT
Regular formula

[B]=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))[/B]

Book1
	A	B	C
1	101ASD234FGHJ	101234	ASDFGHJ
2	2367DFR45GTYDFG	236745	DFRGTYDFG
3	653RTYUHJKKL412	653412	RTYUHJKKL
4	24RTYUIO3123O	243123	RTYUIOO
Sheet3

 

[Sandie1712]

Many Thanks for your reply!!

I am comfortable with substitute solution whereas i am not clear about NPV solution....could you pl through some light on its explanation