Need to separate Alphabets and numbers

[Shyam Gupta]

12ra6j9a87n05
s2u615un85n000y12

How can i separte alphabates and numerical by functions ?

 

[bobhc]

This question must be among the top five posted, the search box top right does work.

 

[PaulF]

Here is what I got from a quick Google search:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

This is an array formula so you will have to press CTRL-SHIFT-ENTER and I'm guessing there are many other ways to do this, but it replaces any number with ""

Respectfully,
PaulF

 

[PaulF]

=SUM(MID(0&D5,LARGE(ISNUMBER(--MID(D5,ROW(INDIRECT("1:"&LEN(D5))),1))*ROW(INDIRECT("1:"&LEN(D5))),ROW(INDIRECT("1:"&LEN(D5))))+1,1)*10^ROW(INDIRECT("1:"&LEN(D5)))/10)

CTRL-SHIFT-ENTER to Extract the Numbers...

Respectfully,
PaulF

 

[Khalid NGO]

Here is what I got from a quick Google search:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

This is an array formula so you will have to press CTRL-SHIFT-ENTER and I'm guessing there are many other ways to do this, but it replaces any number with ""

Respectfully,
PaulF

Hi Paul,
Just a humble note: This formula does not required CSE

Regards,

 

[PaulF]

Hi Paul,
Just a humble note: This formula does not required CSE

Regards,

Now that I go back to the source... This grasshopper moved to fast and looking at the 2nd formula for number that requires CSE, I A$$umed that the 1st for letters needed it as well... but it does work if you CSE it... I did test before posting...

I stand corrected Khalid NGO...

Thank you sir

Respectfully,
PaulF

 

[Khalid NGO]

Now that I go back to the source... This grasshopper moved to fast and looking at the 2nd formula for number that requires CSE, I A$$umed that the 1st for letters needed it as well... but it does work if you CSE it... I did test before posting...

I stand corrected Khalid NGO...

Thank you sir

Respectfully,
PaulF

all right...

I am just a learner here, not sir to be honest

Take care.

 

[Khalid NGO]

You may be interested in view the following:
http://chandoo.org/forum/threads/split-alphanumeric.19920/