[guyfromri]

Hey guys!

I've got a string (ie ABC123) and I'm trying to find a VB function to strip out the alpha characters.

I've done some googling and I could write a function to do this but I'm wondering if theres already a built in function to identify the alpha characters so I can remove them.

I don't have any code yet.....I imagine if there is something, its a word or easy function and I'm not looking for the code, I can write it if I know what I'm looking to use.

As always, thanks in advance!!!

[parbipin]

I have to disappoint you!!! Not in VB...

you have to use a user function.. I know you can write one.

If there are a limited no. of characters in your string then there several ways but if you have all combinations possible to exist then you might want to loop through each character in string and check IsNumeric().

This post has been edited by parbipin: 02 September 2010 - 12:52 PM

[maj3091]

As parbipin has said, that's one way of doing it, although I guess that would give you everything except numbers, so it depends on whether you just want the alpha range or not.

Maybe you could do it with regular expressions (not a syntax I'm familiar with, but I believe it's used for that kind of thing), or you could use a similar idea to parbipin, but check the ascii value of each character falls within the ascii range for a to z or A to Z.

something for you look into.

[bytelogik]

If the numbers appearing in the string are of fixed width (i.e., 3 in case ABC123), then you can just pick up three characters from the string using the function Right().

Example: NumericValue=Right("ABC123",3)

Else,
You need to write your own function to remove the text alphabets.

Good Luck

[parbipin]

Quote

Maybe you could do it with regular expressions (not a syntax I'm familiar with, but I believe it's used for that kind of thing), or you could use a similar idea to parbipin, but check the ascii value of each character falls within the ascii range for a to z or A to Z.

I think this will be a much smarter way than what I suggest, because he has the option to absolutely ignore a group of characters and also to categorize like(numbers, text, special etc).


on what bytelogik suggested: Quite obviously you would have to type cast the result of Right, because that is still going to return a string of numbers.

So you might want:

NumericValue = Val(Right("ABC123",3))


Have Fun!!!

This post has been edited by parbipin: 03 September 2010 - 06:21 AM

[guyfromri]

Fair enough! Thanks for the input, guys.

So, I've read some really long functions online and I believe, as always, that a lot of guys add way too much extra crap to their code to seem fancy. I'm trying to shorten this down and make a logical function out of it but I'm running in to two problems.

Code 1 -- This one removes every other letter starting at 1

Dim str As String
Dim x As Integer
Dim y As String

str = "ABCDEFGHI123456789"
x = 0

Do Until x = Len(str)
    y = Mid(str, x, 1)
    If UCase(y) > Chr(64) And UCase(y) < Chr(91) Then str = Replace(str, y, "")
    x = x + 1
Loop
    
MsgBox str

This one returns BDFH123456789

Code 2 -- I tried starting from the left and working to right

Dim str As String
Dim x As Integer
Dim y As String

str = "ABCDEFGHI123456789"
x = 0

Do Until x = Len(str)
    y = Left(str, x)
    If UCase(y) > Chr(64) And UCase(y) < Chr(91) Then str = Replace(str, y, "")
    x = x + 1
Loop
    
MsgBox str

This one seems to work much better but it comes back with 23456789 -- Its deleting my first number.


Anyone have any input on this? I have gone over it but I can't seem to figure out whats wrong with my positioning.


Again, thanks in advance!!!

[raziel_]

soo you want only the numbers ?
here is my try

Private Sub Command1_Click()
    Dim strTest As String
    Dim strOutput As String
    Dim i As Integer
    
    strTest = "ABC234DEFG3"
    strOutput = ""
    'loop trough the string strTest'
    For i = 1 To Len(strTest)
    'using the ascii code for the numbers only (they are from 48 to 57) we filter them and add them to output string'
        If (Asc(Mid$(strTest, i, 1)) > 47) And (Asc(Mid$(strTest, i, 1)) < 58) Then
            strOutput = strOutput & Mid$(strTest, i, 1)
        End If
    Next i
    'display the output string'
    MsgBox strOutput
End Sub

the problem with the above codes is that they replace the main string witch making the size of the string less then the original. if you dont use while and for like mine code it will result to error because the string will be shorter then the original(if you replace with "" that is)
best luck

EDIT:

not to mention that Left() is the wrong function to use:

Function Left$(String As String, Length As Long) As String
Member of VBA.Strings
Returns a specified number of characters from the left side of a string


as we see the second parameter is length if x increment every time first time it will be "A" second time it will be "AB" third time "ABC" etc.

This post has been edited by NoBrain: 03 September 2010 - 08:22 AM

[parbipin]

Function exNumeric(str As String) As Long

Dim i As Integer
Dim strg As String
Dim chr As String

For i = 1 To Len(str)
    chr = Mid(str, i, 1)
    If IsNumeric(chr) Then strg = strg & chr
Next i

exNumeric = val(strg)

End Function

This is my simple appraoch; I wouldn't recommend Ascii if you are looking for something that does exactly what you want without an overhead.

for a simple reason that you would call Ascii 2 times and Mid 3 times in each loop in this case; while you can get it done in just once call to a function.

[maj3091]

I agree with you here....I misread the original post and was assuming that it was alphas to be kept. DOH!

[guyfromri]

Thanks guys! Makes sense to do it reverse from what I was thinking.

I'm going to use this then implement the same ideas to create the opposite function to retrieve only alpha characters.

Thanks again!!

EDIT--

Just wanted to throw in my final input -- Got this written and it works perfect! Thanks again everyone

Option Compare Database

Dim AlphaInt As Integer
'Dim AlphaStr As String
Dim CurChar As String
Dim str As String
Dim NewStr As String

Sub teststr()

str = "ABC123DEF456"
str = RemoveAlphaCharsFromString(str)

MsgBox str

End Sub

Public Function RemoveAlphaCharsFromString(ByRef AlphaStr As String) As String
'Assumes AlphaStr has been set by the module of form

RemoveAlphaCharsFromString = ""
For AlphaInt = 1 To Len(AlphaStr)
    CurChar = Mid(AlphaStr, AlphaInt, 1)
    If IsNumeric(CurChar) Then RemoveAlphaCharsFromString = RemoveAlphaCharsFromString & CurChar
Next AlphaInt

End Function

This post has been edited by guyfromri: 03 September 2010 - 11:33 AM

[guyfromri]

ok, onneeee last question

I've enhanced my function to be able to remove either Numeric or Alpha characters. It works great now (thanks for the input, again, everyone!)

My question is, is there a way for me to make it so one of the two booleans = true and one has to be false? This way the function can't be called for no reason if they're both false of both true. If not, no big deal but the inquisitive side of me wants to know.

Public Function RemoveCharsFromString(ByRef CurStr As String, ByRef RemoveNumbers As Boolean, ByRef RemoveLetters As Boolean) As String
'Assumes CurStr has been set by the module of form

If RemoveNumbers = True Then
    RemoveCharsFromString = ""
    For StrCnt = 1 To Len(CurStr)
        CurChar = Mid(CurStr, StrCnt, 1)
        If IsNumeric(CurChar) Then RemoveCharsFromString = RemoveCharsFromString & CurChar
    Next StrCnt
ElseIf RemoveLetters = True Then
    RemoveCharsFromString = ""
    For StrCnt = 1 To Len(CurStr)
        CurChar = Mid(CurStr, StrCnt, 1)
        If Not IsNumeric(CurChar) Then RemoveCharsFromString = RemoveCharsFromString & CurChar
    Next StrCnt
End If

End Function

Thanks!

EDIT
or is there a way for me to create my own variable type? Basically, dim TypeToDelete as string....when I get to typetodelete, i get a drop down similar to when selecting a boolean....I don't even know if this is possible but if so that would give me something new to play with and make this easier!


Thanks again!

This post has been edited by guyfromri: 03 September 2010 - 12:22 PM

[keakTheGEEK]

You could try using an enumerator. With an enumerator you could do something like this:

Enum RemoveType
   Letters
   Numbers
End Enum

Then modify your function to take the enumerator type and use this type in your function:

Public Function RemoveCharsFromString(ByRef CurStr As String, ByVal rType As RemoveType) As String  

'Assumes CurStr has been set by the module of form  
SELECT Case rType
    Case RemoveType.Letters
        RemoveCharsFromString = "" 
        For StrCnt = 1 To Len(CurStr)  
            CurChar = Mid(CurStr, StrCnt, 1)  
            If IsNumeric(CurChar) Then 
                RemoveCharsFromString = RemoveCharsFromString & CurChar 
            End If 
        Next StrCnt  
        
    Case RemoveType.Numbers
        For StrCnt = 1 To Len(CurStr)  
            CurChar = Mid(CurStr, StrCnt, 1)  
            If Not IsNumeric(CurChar) Then 
                RemoveCharsFromString = RemoveCharsFromString & CurChar  
            End If
        Next StrCnt  

    Case Else
        Exit Function

End Select 
End Function 

The definition of the enumerator would be declared at the top of your form/Module where you are implementing the RemoveCharsFromString function. I did this code directly in this post (not an ide) and hasn't been tested, but should be enough for you to ponder. Hope this was something close to what you were asking...

~keakTheGEEK

[maj3091]

You could simply use one Boolean, i.e: blnRemovingNumbers, and use it's negative for the other option as you only have the two choices, removing numbers or not.

so...

if blnRemovingNumbers then
   'Remove numbers here
else
   'Remove letters here
end if

[guyfromri]

Thanks again guys! As always, great input and advice.


The enumerator thing is exactly what I wanted. Glad to know it exists...now I have something fun and new to play with!!!!

Thanks again!!!

[guyfromri]

::::::::::::::::::CONCLUSION::::::::::::::::::

Ok! Thanks everyone!!!!

This is what I came up with. It works phenomenal!! Hope it helps someone else going forward

Option Compare Database
Option Explicit

'''''''''ENUMERATORS'''''''''
Enum RemoveType
    RemoveLetters
    RemoveNumbers
End Enum
'''''''''''''''''''''''''''''

Public Function RemoveCharsFromString(ByRef CurStr As String, RemoveCharType As RemoveType) As String
'Assumes CurStr has been set by the module of form

If RemoveCharType = RemoveLetters Then
    RemoveCharsFromString = ""
    For StrCnt = 1 To Len(CurStr)
        CurChar = Mid(CurStr, StrCnt, 1)
        If IsNumeric(CurChar) Then RemoveCharsFromString = RemoveCharsFromString & CurChar
    Next StrCnt
ElseIf RemoveCharType = RemoveNumbers Then
    RemoveCharsFromString = ""
    For StrCnt = 1 To Len(CurStr)
        CurChar = Mid(CurStr, StrCnt, 1)
        If Not IsNumeric(CurChar) Then RemoveCharsFromString = RemoveCharsFromString & CurChar
    Next StrCnt
End If

End Function

Thanks again!!!!!