strip numeric characters from a text field

[Guest]

How can I build a update query that removes all numeric characters in a
field? I have a field that has data such as "3478 PRG Management", "Asc
Chiti Baa", "54 Oracle DBA"... they have from 0 to 6 leading numeric
characters, using the Left function with " " as the separator will not work
since some of the fields do not have numeric chars at all. Please help.
Thanks

PR

 

[Jeff Boyce]

An inelegant (i.e., 'brute force') might be to use VBA code to step through
the string, one character at a time, looking for digits and modifying the
string to leave each one out.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Ugly. Hopefully someone finds a better solution.

IIF(Val("3478 PRG Management")>0,Trim(Mid("3478 PRG Management",
Len(CStr(Val("3478 PRG Management")))+1)), "3478 PRG Management")

 

[John Spencer]

Assumptions:
--The leading numbers are all you want to remove
-- Leading numbers are always followed by one or more spaces and
-- If there are leading numbers there are no letters before the first space

UPDATE YourTable
SET YourField = Mid([Your Field, Instr(1,YourField," ")+1)
WHERE YourField Like "#* *"

BACKUP your data before you run the update. You cannot undo an update
query.

You can see the results by using a SELECT query to "preview" what you will
get

SELECT YourField
, Mid([Your Field, Instr(1,YourField," ") +1) as ProposedValue
FROM YourTable
WHERE YourField Like "#* *"



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks, this worked beautifully!!

Ugly. Hopefully someone finds a better solution.

IIF(Val("3478 PRG Management")>0,Trim(Mid("3478 PRG Management",
Len(CStr(Val("3478 PRG Management")))+1)), "3478 PRG Management")

 

[John Spencer]

Glad that solution is working although if you should have a value like
"001 Occam Blast" it would fail. Since the Val of that is "1" the
truncation of the field would return "01 Occam Blast".

Probably the only way to really handle this well would be to write a
custom function or to use regular expressions
See John Nurick's article at

http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm