View Single Post
  #20   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 Mon, 29 Oct 2007 13:50:03 -0700, Dave
wrote:

Is there a way to do something similar with IsText so that ABC is left, and
123 is removed?

Thanks!
Dave


You could use this UDF, which will return either Text or Digits depending on
the setting of the second (optional) argument:

==========================
Option Explicit
Function TextOrDigits(str As String, Optional Txt As Boolean = True)
Dim re As Object
Dim sPat As String
Const sRes As String = ""

If Txt = True Then
sPat = "\d"
Else
sPat = "\D"
End If

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
TextOrDigits = re.Replace(str, sRes)
End Function
=================================

So to return just Text:

=TextOrDigits(A1)
or
=TextOrDigits(A1,True)

and to return just digits:

=TextOrDigits(A1,False)


--ron