Convert Text field to numeric

I am importing a table into Access from and Excel spreadsheet being sent to me.  I have a field that comes in as:
390-0001
390        - 0013  etc.

(sometimes with spaces and sometimes without)

I need somehow to have these read as

390-1
390-13 etc.

I am not very good with calling code...so I'd like to use a query if possible.


tdverdonAsked:
Who is Participating?
 
ArjiCommented:
I would strip the "-" the same way jez p stripped the spaces out using a two step process but using and 'if then' to do the right ones:

If Instr(strInput," ") AND Instr(strInput,"-") then
  'Strip the spaces and dashes

elseIf Instr(strInput," ") AND NOT Instr(strInput,"-") then
   'Strip the spaces

elseIf Instr(strInput,"-") AND NOT Instr(strInput," ") then
 ' Strip the dashes

end if
0
 
jez_pCommented:
Paste the following code into a module and call it the same way you would any other function.

eg RemoveSpaces([FieldName])

This will give you the whole field with all whitespace stripped out.

Public Function RemoveSpaces(strSearchString As String) As String

On Error GoTo ErrorTrap:


Dim strRemaining As String
Dim intPosition As Integer

    strInput = Trim(strSearchString)
    intPosition = InStr(strInput, " ")
   
    If intPosition > 0 Then
        RemoveSpaces = Left(strInput, intPosition) & RemoveSpaces(Right(strInput, Len(strInput) - intPosition))
    Else
        RemoveSpaces = strInput
    End If
   

Exit Function

ErrorTrap:
    MsgBox Err.Description, vbCritical, strAppTitle
    RemoveSpaces = "Error"
    Exit Function
    Resume
End Function
0
 
jez_pCommented:
Sorry, I didn't finish reading your message.  The above answer will strip out spaces, but it doesn't give you the full answer.  

You will have to rely on some more string manipulation to finish the job.  

Can you rely on the second part of the input string always being 4 digits?
Will there allways be a hyphen separating the two components?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.