Removing numbers from a string

[wwtan]

I want to remove the numbers and letters on the left on a string.

I have some strings which read as such:


ATT 2393747394
AT&T 2732372Tom
CAN Air38263
AA3333333

I tried to use a Left function with InStr within it. Left([Title],(InStr([Title],"#")-1)) The result of that function deleted the whole string. I was wondering whether there is a way to get what I seek.

I seek this result:

ATT
AT&T
CAN Air
AA

Thanks in advance.

[ADezii]

I whipped this up in a hurray, but it should do the trick with little or no modifications:

Public Function fRemoveCharsFromLeft(strString As String) As String
Dim intLen As Integer
Dim intCharCounter As Integer
Dim strBuildString As String

intLen = Len(strString)

'Extract all Characters from the Left of the String until the 1st Number
For intCharCounter = 1 To intLen
  If Not IsNumeric(Mid$(strString, intCharCounter, 1)) Then
    strBuildString = strBuildString & Mid$(strString, intCharCounter, 1)
  Else
    fRemoveCharsFromLeft = Trim(strBuildString)
      Exit Function
  End If
Next
  fRemoveCharsFromLeft = Trim(strBuildString)
End Function

? fRemoveCharsFromLeft("ATT 2393747394")
ATT

? fRemoveCharsFromLeft("AT&T 2732372Tom")
AT&T

? fRemoveCharsFromLeft("CAN Air38263")
CAN Air

? fRemoveCharsFromLeft("AA3333333")
AA

? fRemoveCharsFromLeft("Look guys, not a single number!")
Look guys, not a single number!

? fRemoveCharsFromLeft("6This one number fouled up the works!")
'Returns an Empty String

? fRemoveCharsFromLeft("She sells seashells 9 by the seashore")
She sells seashells

? fRemoveCharsFromLeft()
'Will definately generate an Error!

[missinglinq]

Your duplicate posting of this problem, posted two hours prior to this thread, has been deleted! Please refrain from this prohibited behavior in the future.

From FAQs

Do Not Double Post Your Questions

Double posting is where you start a thread on a topic and then for some reason start another thread on exactly the same topic in the same forum. Please do not do this because

1. It makes it very hard for people to answer you especially if there are answers happening in all the threads you have started because they have to read 2 or more threads in order to see what has already been said.

2. It swamps the forum with your problem resulting in less attention for the other threads.

If you feel for some reason that you post has been overlooked (for instance it hasn't had any replies after 24 hours) please do not repost the question. Post a message to the thread you started, this will bump it back to the top of the thread list for the forum.

Thank you for your attention in this matter.

Linq ;0)>

Moderator