View Single Post
  #14   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Remove Numbers from Alphanumeric String

On Fri, 26 Oct 2007 11:03:00 -0700, Dave
wrote:

Hi,

I have a value in Cell A of ABC123.

I want Cell B1 to contain the 123 from this cell.

Can anyone tell me the formula to enter in B1. I'm sure I've seen it
somewhere before but can't put my finger on it.

Thanks!
Dave


Assuming the digits are all contiguous:

B1:

=LOOKUP(9.99E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

If the digits are not contiguous, you can use this UDF:

=======================
Option Explicit
Function Digits(str As String)
Dim re As Object
Const sPat As String = "\D"
Const sRes As String = ""

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
Digits = re.Replace(str, sRes)
If IsNumeric(Digits) Then Digits = CDbl(Digits)
End Function
===========================

To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code above into the window that opens.

You can then use the formula =Digits(cell_ref) in any cell. e.g.

B1: =Digits(A1)
--ron