Opposite to Val

[IanT]

Can anyone tell me what the opposite to the Val function, I only want letters from a string of data!

 

[Sammy B]

I found this in MS Access help. (I know if you do not know what to look for it does no good but I just came accross it one day.)

Str Function


Returns a Variant (String) representation of a number.

Syntax

Str(number)

The required number argument is a Long containing any valid numeric expression.

Remarks

When numbers are converted to strings, a leading space is always reserved for the sign of number. If number is positive, the returned string contains a leading space and the plus sign is implied.

Use the Format function to convert numeric values you want formatted as dates, times, or currency or in other user-defined formats. Unlike Str, the Format function doesn't include a leading space for the sign of number.

Note The Str function recognizes only the period (.) as a valid decimal separator. When different decimal separators may be used (for example, in international applications), use CStr to convert a number to a string.

This may be what ypu are looking for.

Sam

 

[raskew]

Hi -

The Str() function, used be itself, displays a leading space, e.g.:

X = 52
? str(X)
52

To eliminate the leading space, use the ltrim() function as a wrapper, e.g.:

X = 52
? ltrim(str(X))
52

HTH - Bob

 

[raskew]

Oops -

Just reread your initial post (always helpful). To preserve just the alpha characters in a string, try saving this to a new module. Then follow the comments to test in the debug (immediate) window.

Function SaveAlpha(pStr As String) As String
'*******************************************
'Purpose:   Removes non-alpha characters from
'           a string
'Coded by:  raskew
'Calls:     Function IsAlpha()
'Inputs:    ? SaveAlpha(" t#he *qu^i5ck !b@r#o$w&n fo#x ")
'Output:    the quick brown fox
'Note:      As written, empty spaces are ignored.
'*******************************************

Dim strHold As String
Dim intLen As Integer, n As Integer

    strHold = Trim(pStr)
    intLen = Len(strHold)
    n = 1
    Do
       If Mid(strHold, n, 1) <> " " And Not IsAlpha(Mid(strHold, n, 1)) Then
          strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
          n = n - 1
       End If
       n = n + 1
    Loop Until Mid(strHold, n, 1) = ""
    SaveAlpha = strHold
    
End Function

Function IsAlpha(strIn As String) As Boolean
'*******************************************
'Purpose:   Determine if a character is alpha
'           i.e. "a" - "z" or "A" - "Z"
'Inputs:    ? IsAlpha("4"),
'Output:    False
'*******************************************

Dim i As Integer

    i = Switch(Asc(strIn) > 122 Or Asc(strIn) < 65, 1, _
        InStr("91 92 93 94 95 96", Asc(strIn)) > 0, 2, _
        True, 3)
    IsAlpha = IIf(i = 3, True, False)

End Function

HTH - Bob