• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Can you help in extracting the numbers from this string

I have a string

mystring="SEA123_POT

The first 3 letters of my string are always letters
and the remaining between the last letter and the "_" are numbers
I thought of using instr but I dont know how?

I only want to get the numbers in the case above "123"

So i thought of using
Dim TMP As String
TMP = Text1.Text
For a = 65 To 90
TMP = Replace(TMP, Chr(a), "")
Next a

MsgBox TMP

but it gets also the "_" which i dont want.

can you help?
0
Gabriel_Brienza
Asked:
Gabriel_Brienza
1 Solution
 
marconovaroCommented:

Have you got always three letters before the number?

    Dim mystring  As String
    Dim myValue As Long
    Dim pos As Long
   
    mystring = "SEA123_POT"
    pos = InStr(1, mystring, "_", vbTextCompare)
   
    myValue = Val(Mid$(mystring, 4, Len(mystring) - pos))
   
M
0
 
Gabriel_BrienzaAuthor Commented:
Tried that but doesnt work.

mystring always have
                    1) 3 letters at the beginning
                    2) numbers (which can be many)
                    3) _ (underscore)
                    4) and letters

mystring="sea12112_pot"
or
mystring "sea123_pot"

I only want to get the numbers
 
any more suggestions
thanks
0
 
marconovaroCommented:

Sorry: this should work:

    Dim mystring  As String
    Dim myValue As Long
    Dim pos As Long
   
    mystring = "SEA1_POT"
    pos = InStr(1, mystring, "_", vbTextCompare)
   
    myValue = Val(Mid$(mystring, 4, pos - 3))

M
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DocMCommented:
Hi Gabriel,

Dim TMP As String
TMP = Text1.Text

TMP = Mid(TMP, 4, 3)
 
MsgBox TMP
0
 
Gabriel_BrienzaAuthor Commented:
thanks a lot it worked

i always have problem with instr

thanks again
gabriel
0
 
livziCommented:




mid("ABC123_dfghgsd",4,instr(4,"ABC123_dfghgsd","_") - 4)
0
 
fluglashCommented:
if you have a string that begins always on "sea" and finishes on "_pot" use following:

mystring = left( mystring, len(mystring) - 4)
mystring = right(mystring, len(mystring) - 3)

or use replace() function
0
 
Gabriel_BrienzaAuthor Commented:
the letters at the end and at the beginning
can be anything.

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now