Removing special characters and numbers from text field

[mlrucci]

Good afternoon, I have imported a spreadsheet from excel to access. I have a filed called Document ID. I would like to remove special characters from this to create a new number. This is essential in creating a 1:1 relationship on another table.

Original value:
0871900602-601116-7-1



New Value: Need to delete all preceding 0s, delete anthing after the second "-" and removed the "-" in the middle. End result should be:
871900602601116

Suggestions?
Actually completely lost as to how to even start.

Any help would be appreciated.

[Welshgasman]

Use split() function? And instrrev()

[ranman256]

put this function in a module ,
then run an update query to post the correction in a NEW FIELD. (don't overwrite the original value)
or
run the correction at import if using a query.

usage:

FixNum([FIELD])

Public Function FixNum(ByVal pvNum)
Dim vRet, vChr
Dim iDashCt As Integer, i As Integer
On Error Resume Next

  'remove leading zeros
vChr = Left(pvNum, 1)
While vChr = "0"
  pvNum = Mid(pvNum, 2)
  vChr = Left(pvNum, 1)
Wend

'now keep nums
For i = 1 To Len(pvNum)
   vChr = Left(pvNum, 1)
   Select Case True
      Case vChr = "-"
        iDashCt = iDashCt + 1
        pvNum = Mid(pvNum, 2)
        If iDashCt = 2 Then GoTo endit
        
      Case Asc(vChr) > 47 And Asc(vChr) < 58
        vRet = vRet & vChr
        pvNum = Mid(pvNum, 2)
   End Select
   
Next

endit:
FixNum = vRet
End Function

[mlrucci]

Ranman256,
Thank you for your response. I am fairly new to coding and am self-taught. I can create the module and I copied/pasted the code provided. I created a new field in the table. Not sure how to run the module to then run the update query (I'm familiar with update qrys, just not using a module). Can you please provide me with some additional information?

[Gicu]

Original value:
0871900602-601116-7-1

Are the original numbers consistent? If yes you can use something as simple as this in a calculated field in a query:
FixNumber=Replace(Mid(OriginalNumber,2,16),"-","")

Cheers,

[mlrucci]

Thank you all for your help. I wound up using Gicu's suggestion and did an update qry onto the table. Thanks again! Works great

[Micron]

So you will never have something like 0071900602-601116-7-1 or 0871900602-601-7-1 or some other deviation from what you posted?

[orange]

Can you tell us more about this relationship?

This is essential in creating a 1:1 relationship on another table.

[mlrucci]

the 1:1 relationship number is embedded in the Original value:0871900602-601116-7-1. After removing the necessary characters and numbers, it will become a 1:1 relationship with another field in a different table. The end result is 871900602601116. In other words, by removing preceding 0, all-, and anything after the second -, I get what I need. All the numbers are in the same format. This number is created by a different platform and cannot be adjusted. Hope that is clear as mudd.

[Gicu]