Trimming and clensing a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Before I embark on writing a bloated function to do it, can anyone suggest
the easiest way of solving this problem...

I have a telephone number field which people will probably fill in something
like;

(01234) 567 890
or
01234 567 890

I don't want to fix the formats using input masks of validation rules, as I
want them to use what ever format makes them comfortable and makes it easier
to read BUT I also need to output the data to another application in the
format;

01234567890

Basically, is there an easy way of removing all non-numeric characters from
a string?

Thanks

Nick
 
Basically, is there an easy way of removing all non-numeric characters
from
a string?
Click to expand...

The following function will remove all non-numeric characters from a string:

Public Function NumericOnly(sSource As String) As String

Dim BytesIn() As Byte
Dim BytesOut() As Byte
Dim LenBytes As Integer
Dim IdxIn As Integer
Dim IdxOut As Integer

If Nz(sSource, "") = "" Then
NumericOnly = ""
Exit Function
End If

BytesIn() = StrConv(sSource, vbFromUnicode)
LenBytes = UBound(BytesIn)

ReDim BytesOut(0 To LenBytes)

For IdxIn = 0 To LenBytes
If BytesIn(IdxIn) > 47 And BytesIn(IdxIn) < 58 Then
BytesOut(IdxOut) = BytesIn(IdxIn)
IdxOut = IdxOut + 1
End If
Next

NumericOnly = StrConv(BytesOut, vbUnicode)

End Function

-LGC
 
NH said:
Before I embark on writing a bloated function to do it, can anyone suggest
the easiest way of solving this problem...

I have a telephone number field which people will probably fill in something
like;

(01234) 567 890
or
01234 567 890

I don't want to fix the formats using input masks of validation rules, as I
want them to use what ever format makes them comfortable and makes it easier
to read BUT I also need to output the data to another application in the
format;

01234567890

Basically, is there an easy way of removing all non-numeric characters from
a string?
Click to expand...


There's no built-in function to do that, but I don't think
it requires a "bloated" user defined function. Here's an
outline to get you started:

Public Function Strip(strPhone As String) As String
Dim k As Integer

For k = 1 To Len(strPhone)
If Mid(strPhone, k, 1) Like "#" Then
Strip = Strip & Mid(strPhone, k, 1)
End If
Next k
End Function
 
Ahh.. thank you both for your help.

My function would have been bloated in that I was going to loop throught the
string testing each character for a "0", then a "1", then a "2",.... through
to "9".....

You have saved me loads of work. Thanks again.

Nick
 
Back
Top