Getting Result excluding number Character where number and Alphabets character mixed in same field

[joshynaresh]

Dear Sir,



I want to get result only alphabets character if there are alphabet and number characters in same field. Can it is possibe to extract only Alphabet character from mixed character in same field. for example I hvae uploaded JPG. There is two types of Required Result. 1 is excluding number only and another reuiered result is excluding number and other special charecter (Alphabets only



Thanks in advance.

[June7]

This needs a VBA custom function. I am sure question has been asked many times. Here is one http://www.utteraccess.com/forum/lof.../t1124576.html

[JSR]

It's not going to be exactly what you want, because for #3, you want to replace (space)977(space) with (space), whereas with #6, you want to replace 34 with (space). That kind of logic would be more difficult to program than what I just wrote:

Public Function RemoveNums(InputStr As String) As String
'remove numbers and duplicate spaces that result from numbers surrounded by spaces

Dim ResultStr As String
Dim L As Integer
Dim m As String
Dim i As Integer

ResultStr = ""      'initialize

For i = 1 To Len(InputStr)
  m = Mid(InputStr, i, 1)
  
  If Asc(m) >= 48 And Asc(m) <= 57 Then      'digits 0-9
    'skip it
  Else
    ResultStr = ResultStr & m
  End If
Next i

'replace duplicate spaces with one space
L = Len(ResultStr)
i = 1

Do While i < L
  If Mid(ResultStr, i, 2) = "  " Then
    ResultStr = Left(ResultStr, i) & Right(ResultStr, Len(ResultStr) - i - 1)
    L = L - 1       'length is one less due to removal of duplicate space
  Else
    i = i + 1       'move to the next character (leave i alone in case of 3 or more duplicate spaces in a row)
  End If
Loop

'delete trailing space
If Right(ResultStr, 1) = " " Then
  ResultStr = Left(ResultStr, Len(ResultStr) - 1)
End If

RemoveNums = ResultStr

End Function

Public Function RemoveAllButLetters(InputStr As String) As String

Dim ResultStr As String
Dim L As Integer
Dim m As String
Dim i As Integer

ResultStr = ""      'initialize

For i = 1 To Len(InputStr)
  m = Mid(InputStr, i, 1)
  
  If (Asc(m) >= 97 And Asc(m) <= 122) Or (Asc(m) >= 65 And Asc(m) <= 90) Or Asc(m) = 32 Then    'a-z or A-Z or space
    ResultStr = ResultStr & m
  End If        'else skip it
Next i

'replace duplicate spaces with one space
L = Len(ResultStr)
i = 1

Do While i < L
  If Mid(ResultStr, i, 2) = "  " Then
    ResultStr = Left(ResultStr, i) & Right(ResultStr, Len(ResultStr) - i - 1)
    L = L - 1       'length is one less due to removal of duplicate space
  Else
    i = i + 1       'move to the next character (leave i alone in case of 3 or more duplicate spaces in a row)
  End If
Loop

'delete trailing space
If Right(ResultStr, 1) = " " Then
  ResultStr = Left(ResultStr, Len(ResultStr) - 1)
End If

RemoveAllButLetters = ResultStr

End Function

Then, you simply run a query:
select SN, RemoveNums([NameField]) from [table name]

and:
select SN, RemoveAllButLetters(RemoveNums([NameField])) from [table name]

which will produce:

3   Mr. Shyam 977 Nepal    Mr. Shyam Nepal    Mr Shyam Nepal
6   Sohan 34-23-56         Sohan --           Sohan