ExcelBanter - Remove number from text string

On Thu, 20 Mar 2008 04:04:00 -0700, adunao
wrote:

Please how do I remove numbers from unformatted texts strings e.g
DEPOSIT RICA MARINE 6890312 RICA MARINE
or
DEPOSIT 7048783 KURUS NIG
or
DEPOSIT 7042248 NNAJI 7042248 NNAJI
Pls I need a plobal formula for these

This UDF will remove all the digits from a text string, returning only the text
portions.

To enter this, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter the formula =RemDigits(cell_ref) into some cell where
cell_ref is your target cell (e.g. A1).

=============================
Option Explicit
Function RemDigits(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function
==========================
--ron