Extract Numerical Portion of String

[lezawang]

Hi
I am trying to understand the code below. I have 2 questions now

1) What is the idea of using Mid() function
2) What does &amp mean.

Temp=Temp&Mid(Phrase,Current_Pos,1) 

Function Extract_Number_from_Text(Phrase As String) As Double
Dim Length_of_String As Integer
Dim Current_Pos As Integer
Dim Temp As String
Length_of_String = Len(Phrase)
Temp = ""
For Current_Pos = 1 To Length_of_String
If (Mid(Phrase, Current_Pos, 1) = "-") Then
  Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
If (Mid(Phrase, Current_Pos, 1) = ".") Then
 Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
If (IsNumeric(Mid(Phrase, Current_Pos, 1))) = True Then
    Temp = Temp & Mid(Phrase, Current_Pos, 1)
 End If
Next Current_Pos
If Len(Temp) = 0 Then
    Extract_Number_from_Text = 0
Else
    Extract_Number_from_Text = CDbl(Temp)
End If
End Function

souce:
https://www.automateexcel.com/vba/extract-number-from-string/[/COLOR][/COLOR]

 

[kweaver]

The code above is correct because it uses the & (ampersand) versus what you see in the code on that link. The code in the link gets translated to an ampersand here, for example.
The ampersand is used to concatenate.


The MID function finds the mid-range of the text (e.g., whatever Phrase is) starting in the Current_Pos position and selecting 1 character.

 

[DanteAmor]

Hi
I am trying to understand the code below. I have 2 questions now

1) What is the idea of using Mid() function
2) What does &amp mean.

I'll explain it to you with an example.

In this sentence:
da123m


The code does the following:

Phrase =  [COLOR=#0000ff]"da123m"[/COLOR]
For [COLOR=#333333]Current_Pos [/COLOR]= 1 to 6  '(len phrase)
      [COLOR=#333333]Current_Pos [/COLOR]= 1 ([COLOR=#333333]Current_Pos [/COLOR]initial with 1)
     If (IsNumeric(Mid(Phrase, [COLOR=#333333]Current_Pos [/COLOR], 1))) = True Then
     if isnumeric(mid("[COLOR=#0000ff]d[/COLOR]a123m", 1, 1) = true then
     if isnumeric("d") = true then
       Temp = Temp & Mid(Phrase, Current_Pos, 1)
     End If
next

For [COLOR=#333333]Current_Pos [/COLOR]= 1 to 6  '(len phrase)
      [COLOR=#333333]Current_Pos [/COLOR]= 2 (now is 2)
     If (IsNumeric(Mid(Phrase, [COLOR=#333333]Current_Pos [/COLOR], 1))) = True Then
     if isnumeric(mid("d[COLOR=#0000ff]a[/COLOR]123m", 2, 1) = true then
     if isnumeric("a") = true then
       Temp = Temp & Mid(Phrase, Current_Pos, 1)
     End If
next

For [COLOR=#333333]Current_Pos [/COLOR]= 1 to 6  '(len phrase)
      [COLOR=#333333]Current_Pos [/COLOR]= 3 (now is 3)
     If (IsNumeric(Mid(Phrase, [COLOR=#333333]Current_Pos [/COLOR], 1))) = True Then
     if isnumeric(mid("da[COLOR=#0000ff]1[/COLOR]23m", 3, 1) = true then
     if isnumeric("1") = true then
       Temp = Temp & Mid(Phrase, Current_Pos, 1)
       Temp = "" & "1"
     End If
next

For [COLOR=#333333]Current_Pos [/COLOR]= 1 to 6  '(len phrase)
      [COLOR=#333333]Current_Pos [/COLOR]= 4 (now is 4)
     If (IsNumeric(Mid(Phrase, [COLOR=#333333]Current_Pos [/COLOR], 1))) = True Then
     if isnumeric(mid("da1[COLOR=#0000ff]2[/COLOR]3m", 4, 1) = true then
     if isnumeric("2") = true then
       Temp = Temp & Mid(Phrase, Current_Pos, 1)
       Temp = "1" & "2"
     End If
next

For [COLOR=#333333]Current_Pos [/COLOR]= 1 to 6  '(len phrase)
      [COLOR=#333333]Current_Pos [/COLOR]= 5 (now is 5)
     If (IsNumeric(Mid(Phrase, [COLOR=#333333]Current_Pos [/COLOR], 1))) = True Then
     if isnumeric(mid("da12[COLOR=#0000ff]3[/COLOR]m", 5, 1) = true then
     if isnumeric("3") = true then
       Temp = Temp & Mid(Phrase, Current_Pos, 1)
       Temp = "12" & "3"
     End If
next

For [COLOR=#333333]Current_Pos [/COLOR]= 1 to 6  '(len phrase)
      [COLOR=#333333]Current_Pos [/COLOR]= 6 (now is 6)
     If (IsNumeric(Mid(Phrase, [COLOR=#333333]Current_Pos [/COLOR], 1))) = True Then
     if isnumeric(mid("da123[COLOR=#0000ff]m[/COLOR]", 6, 1) = true then
     if isnumeric("m") = true then
       Temp = Temp & Mid(Phrase, Current_Pos, 1)
       Temp = "12" & "3"
     End If
next

Extract_Number_from_Text = CDbl(Temp)
Extract_Number_from_Text = CDbl("123")
Extract_Number_from_Text = 123