removing letters

Posted by mcarter on February 13, 2002 6:54 AM

how can i remove letters from a data cell

example:

a2: ed 11 22 33 44 55 mary

is there an if statement that will remove all letters and display just 11 22 33 44 55?

thanks.

Posted by Robin on February 13, 2002 9:19 AM

Does the text always appear at the beginning and the end of the cell contents?

Posted by mcarter973 on February 13, 2002 10:02 AM

Re: Does the text always appear at the beginning and the end of the cell contents?

Posted by Aladin Akyurek on February 13, 2002 1:16 PM

Your reply to Robin's question has been cannibalized by the web script, so I don't know whether all of your strings have the regular structure of your example. If so, and assuming that A1 holds the first string from which to extract the "number part":

In B1 enter: =TRIM(SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),""))

In C1 enter: =TRIM(SUBSTITUTE(B1,SUBSTITUTE(B1,RIGHT(B1,SUMPRODUCT((LEN(B1)-LEN(SUBSTITUTE(B1,{" ",0,1,2,3,4,5,6,7,8,9},""))))),""),""))

Note. Using VBA for this task would be equally good, perhaps even better. I leave that to the VBA army aound the board.

===================