Replacing letters & symbols with numbers

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

Guest

I receive files that include IBM numbers. The last position has either a letter or symbol representing the last number. I need a way to convert the symbol/letter to a number, including negatives and format it to currency. Does anyone have any ideas?
 
Create a table with a listing of symbol/letters and
another field of what you want to convert them to. then
you could use the string manipulation functions to update
your dataset.


-----Original Message-----
I receive files that include IBM numbers. The last
Click to expand...
position has either a letter or symbol representing the
last number. I need a way to convert the symbol/letter to
a number, including negatives and format it to currency.
Does anyone have any ideas?
 
Here are a couple of rough and ready functions I wrote when this came up
some time ago. ConvertSignedChar() is a helper function for
ConvIBMSignedField().



Function ConvertSignedChar(C As String) As String
'Converts a single signed character to
'digit with minus sign if needed
Dim j As Long

j = InStr("{ABCDEFGHI", C) - 1
If j >= 0 Then
ConvertSignedChar = CStr(j)
Else
j = InStr("}JKLMNOPQR", C) - 1
If j >= 0 Then
ConvertSignedChar = "-" & CStr(j)
Else
ConvertSignedChar = C
End If
End If
End Function

Public Function ConvIBMSignedField(V As Variant) As Long
'Converts variant(string) containing signed field
'Modify for other numeric types
Dim blNegative As Boolean
Dim strTemp As String
Dim strSignedChar As String

strTemp = CStr(V)
'Either first or last character is signed
If InStr("0123456789", Right(strTemp, 1)) Then
'last char is numeric so first is signed
strSignedChar = ConvertSignedChar(Left(strTemp, 1))
If Left(strSignedChar, 1) = "-" Then
strSignedChar = Mid(strSignedChar, 2)
blNegative = True
Else
blNegative = False
End If
strTemp = strSignedChar & Mid(strTemp, 2)
Else
'Last char is signed
strSignedChar = ConvertSignedChar(Right(strTemp, 1))
If Left(strSignedChar, 1) = "-" Then
strSignedChar = Mid(strSignedChar, 2)
blNegative = True
Else
blNegative = False
End If
strTemp = Left(strTemp, Len(strTemp) - 1) & strSignedChar
End If
ConvIBMSignedField = CLng(strTemp) * IIf(blNegative, -1, 1)
End Function


I receive files that include IBM numbers. The last position has
Click to expand...
either a letter or symbol representing the last number. I need a way to
convert the symbol/letter to a number, including negatives and format it
to currency. Does anyone have any ideas?
 
I said they were rough and ready: the data source I built them for
didn't include any empty fields. You'll need to decide how you want to
handle Nulls in the data and modify the function accordingly: e.g.
change the return type from Long to Variant and insert something like

If IsNull(V) Then
ConvIBMSignedField = Null
Exit Function
End If

immediately before
 
Back
Top