stripping superfluous data from strings

[Roger PB]

I download bank statements electronically, but one field is particularly
tedious. It contains a string such


Privac 000006336442 OG/000000226|32000 00900699090 JONES
plc

I have Richard Shepherd's book on Excel VBA programming, which explains how
to remove or replace a symbol in a string, but I would like to know the
syntax for stripping ALL numeric characters from a string, or alternatively
all numeric (or alphabetic) characters preceding (or following) a symbol
such as the "|" (vertical bar) in the example given above. Can anyone help a
VBA beginner?

 

[Greg Maxey]

Here is one way. Select the text and run this macro:

Sub StripNumerics()

Dim oChrNum As Long

Dim i As Long

Dim myStr As String

Dim tmpStr As String



myStr = Selection.Range.Text



For i = 1 To Len(myStr)

oChrNum = Asc(Mid(myStr, i, 1))

If oChrNum <= 47 Or oChrNum >= 58 Then

tmpStr = tmpStr + Chr(oChrNum)

End If

Next i

myStr = tmpStr

Selection.Range.Text = myStr



End Sub

 

[Roger PB]

Thanks, Greg,

But when I ran your routine I got an error message for
myStr=Selection.Range.Text.
"Wrong Number of arguments or invalid property assignment."

I tested the routine with a line like "myStr= "1234yuo1234yo1234" and could
figure out how it works.

But then I ran into the problem of how to get the modified string back into
the desired cell.

Selection.Range.Text = myStr didn't work.

Books like XL Programming for Dummies always seem to content themselves by
putting the answer in a message box. But I usually want to to is to put a
variable into a specific cell.

 

[Greg Maxey]

Roger,

I just tested the code you sent back with the string:
1234yuo1234yo1234 selected in the document

After running the macro the result in the document was:
yuoyo

That is what you want correct?

This one lets you enter the string in an input box and display the result in
a specific table cell. Let me know if you have further problems.
Sub StripNumerics()

Dim oChrNum As Long
Dim i As Long
Dim myStr As String
Dim tmpStr As String

myStr = InputBox("Enter a string to strip")
For i = 1 To Len(myStr)
oChrNum = Asc(Mid(myStr, i, 1))
If oChrNum <= 47 Or oChrNum >= 58 Then
tmpStr = tmpStr + Chr(oChrNum)
End If
Next i
myStr = tmpStr
'Put in first table in document, row 1, column 1
ActiveDocument.Tables(1).Cell(1, 1).Range.Text = myStr
End Sub

 

[Roger PB]

Greg,

I am an idiot.

I wanted to do this procedure in Excel, but mailed this to the Word group --
unfortunately there does not seem to be an equivalent group for Excel VBA
beginners.

Nevertheless, with the help of the technique you showed me, I have now found
a way to achieve what I want in Excel, i.e. to replace the contents of all
the cells in a particular column with a modified string eliminating the
digits. The technique you have taught me can similarly be applied for
removing any other characters, if one knows the ASCII codes.

May I add that I find ALL these VBA groups very helpful, because the help
features of VBA and the books I own dont always cover the problems that
confront me, and I find myself copying and pasting many tips into a
tip-book so as to learn points of syntax and useful techniques.

Whereas most of the books on VBA seem to be equivalent to "Teach Yourself
Hindu", with lots of syntax and grammar, there seems to be no VBA equivalent
to "Hindu Phrasebook" with examples of code useful in many situations. Or
do you know of one?

Thanks again,

Roger

 

[Greg Maxey]

Roger,

I don't know of such a book. I have never read a book on VBA and if your
description of said book is accurate, I likely never will . What little
I know about VBA is monkey see, monkey do from tips picked up here in the
Word VBA newsgroups and from asking a lot of questions.

If you don't know the code for a particular character you can select the
character in your document and then type the following in the VB Editor
immediate window:

? Asc(Selection.Text) and press enter.

 

[Helmut Weber]

Hi,

and try AscW in addition.

SCNR

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/