How to remove, strip periods or fullstops or dots and hyphens?

[rbel]

I was wondering what code will effectively remove, strip kill etc,. any periods or fullstops or dots and hyphens from a field.(e.g. Tax N°: 96.567.402-K)
This being an alpha numeric combination which could have any number or letter after the hyphen like
2,3,4,5,6,7,8,9,0,K,L etc,.In responce to "Remove spaces, but not all, from within a string", I have tried out an example Norris68 suggested on Aug 27, 2003 however this didn't do the trick.

I hope you can help me out as I have about 5000 records which on a montly basis are increasing, thus the same thing has to be done ongoing.

In the mean time awaiting for any body to help me out.

 

[TonyJollans]

Hi rbel,

I checked out thread705-639652 and it's not exactly what you want but it's the same idea. There is no function I know which will do it all in one go but, assuming you have A2K, you can use the Replace function which Norris68 used ..

YourString =

Replace(Replace(

YourString

, ".", ""), "-", "")

If you don't have 2000 you will need a custom function. Please come back if that's the case.

Enjoy,
Tony

 

[rbel]

HiTonyJollans

I tried the code using what you suggested but no go. Here is what I have done:

Public Function Remove(InText As String) As String
Dim R As String
R = InText
Do
R = Replace(Replace(R, ".", "&quot, "-", "&quot
Loop Until InStr(R, "&quot = 0
Remove = R
End Function

And using a SQL statement such as:
UPDATE tblClientes SET "CODIGO NACIONAL" = Remove("CODIGO NACIONAL&quot

I have included some real data samples for you to have a better idea of what I mean.

"CODIGO NACIONAL" (Field Name)
96.573.100-8
79.829.500-4
96.820.180-8
84.000.000-1
78.383.450-2
96.613.750-9
96.688.810-5
96.526.080-3
96.646.360-0
78.029.120-6
96.439.000-2
96.681.790-9
83.252.500-3
78.036.610-9
79.500.510-2
78.101.430-3
96.647.510-2
96.728.570-6
78.488.500-3
78.062.300-4
96.615.800-K
85.146.900-1
79.527.050-7
93.845.000-5
96.518.750-2

By the way we are using Office XP (Microsoft Access 2002).
Hoping that this may give you a better Idea. Would it be possible from these samples if you could write the proper code I should be using, I am sure I have overlooked something.

regards
rbel

 

[CajunCenturion]

I think you can do this using Regular Expressions, but in order to get the pattern correct, there are a couple of questions.

What should Remove("Tax N°: 96.567.402-K&quot return?
What should Remove("CODIGO NACIONAL&quot return?
What should Remove("96.573.100-8&quot return?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HomeALone]

If each record is with fixed length (which means the period or dash always falls in the same position), you can try extract them and concatenate to exclude what you don't want. for example, build a query as below

NewFieldName: Left([FieldName],2) & Mid([FieldName],4,3) & Mid([FieldName],8,3) & Right([FieldName],1)

 

[TonyJollans]

Hi rbel,

You've made it more complicated than it needs to be. The Remove routine just needs a single statement, like this:

Public Function Remove(InText As String) As String
 Remove = Replace(Replace(InText, ".", ""), "-", "")
End Function

.. and the SQL should be ..

UPDATE tblClientes SET tblClientes.[CODIGO NACIONAL] = remove([CODIGO NACIONAL]);

Enjoy,
Tony

 

[SgtJarrow]

check out the following function...used like:

retval = dhTranslate(stringtoconvert, ".- ". "&quot

will replace any ., -, or space with nothing

' ***********************************************
Public Function dhTranslate(ByVal strIn As String, ByVal strMapIn As String, _
ByVal strMapOut As String) As String

Dim lngI As Long
Dim lngPos As Long
Dim strChar As String * 1
Dim strOut As String

'If there's no list of characters to replace, there's no point going on with the work
If Len(strMapIn) > 0 Then
'Right fill the strMapOut set
If Len(strMapOut) > 0 Then
strMapOut = Left$(strMapOut & String(Len(strMapIn), Right$(strMapOut, 1)), Len(strMapIn))
End If
For lngI = 1 To Len(strIn)
strChar = Mid$(strIn, lngI, 1)
lngPos = InStr(1, strMapIn, strChar, vbBinaryCompare)
If lngPos > 0 Then
'If strMapOut is empty, this doesn't fail, because Mid handles empty strings gracefully
strOut = strOut & Mid$(strMapOut, lngPos, 1)
Else
strOut = strOut & strChar
End If
Next lngI
End If
dhTranslate = strOut

End Function
' ******************************************


****************************
When the human body encounters disease it raises its temperature making it uncomfortable not only for the body but also for the disease. So it global warming the Earth's way of saying we are not wanted?

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com