• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel formula needed

How to separate text and numbers from an alphanumeric data. e.g.,
3489djffdj3489kdf 34893489 djffdjkdf
Hi,

Both of these are ARRAY formula, see below for how to enter them.

For the numbers.

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($A$1:$A$300)-1)*ISNUMBER(-MID("01"&A1,ROW($A$1:$A$300),1)),ROW($A$1:$A$300))+1,1),10^(300-ROW($A$1:$A$300))),2,300)

And a bit long but for the letters.

=SUBSTITUTE(SUBSTITUTE(A1,LOOKUP(1E+100,--MID(MID(A1,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")),MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),""))),SMALL(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")),MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")))&"0123456789"),1),ROW(INDIRECT("1:"&LEN(MID(A1,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")),MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")))))))),""),LOOKUP(1E+100,--MID(A1,SMALL(FIND({0,1,2,3,4,5,6,7,8,9},$A$1&"0123456789"),1),ROW(INDIRECT("1:"&LEN($A$1))))),"")


This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.
 
Hi Mike,

The result is incorrect for numerical part. Perhaps due to this:
http://support.microsoft.com/kb/269370

Result is 3489348934893500 and it should be 3489348934893489.

If there are no numbers with decimal places then following can be used for the second part.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")
 
Hi Shrivallabha ,

The question itself is not a model of how a question should be phrased ; it is open to any number of interpretations.

I read the single line as containing both the input and output !

The input is :

3489djffdj3489kdf

The output is :

Numeric values concatenated together : 34893489

Alphabets concatenated together : djffdjkdf

But yes , you are right that when the numeric values concatenated together exceed 15 digits , the formula can result in problems.

Narayan
 
Back
Top