VBA Code to Extract Number After a $ and also Extract Number before % sign.

[OilEconomist]

Thanks in advance for your assistance. I have searched all over the web and throughout different forums, but I could not find anything simple enough to modify or use as a starting point.

I would like a function which will extract a number when either followed by a special character or ending with a special character.

The function would be something like where I would specify if I am trying to find data before the character or after. The following is a sample code which I have started and at the end I have placed some sample data.

Option Explicit

Sub Sample()
Dim i As Long
Dim j As Long
Dim LRC As Long
Dim ColNo As Long
Dim aCell As Range
Dim Rng As Range
Dim News As String
Dim CharacterSpecial As Variant
Dim WhereLocated As String
Dim NumberExtract As Variant


'Set Range
    ColNo = 7
    LRC = Cells(Rows.Count, ColNo).End(xlUp).Row
    Set Rng = Range(Cells(9, ColNo), Cells(LRC, ColNo))


For Each aCell In Rng
    
    News = Cells(aCell.Row, ColNo)
    For j = 4 To 5
        
        If j = 4 Then
            WhereLocated = "Before"
            CharacterSpecial = "$"
        ElseIf j = 5 Then
            WhereLocated = "After"
            CharacterSpecial = "%"
        End If
            
        NumberExtract = NumberExtractF(News, CharacterSpecial, WhereLocated)
        Cells(aCell.Row, j) = NumberExtract
        
    Next j

Next


End Sub


Function NumberExtractF(News, CharacterSpecial As Variant, WhereLocated As String) As Variant

    If WhereLocated = "Before" Then
        'Extract the number and the character before it
    ElseIf WhereLocated = "After" Then
        'Extract the number and the character after it
    End If

End Function

Sample data with results are as follows:

Stocks - Analysis Tool, Sub - 52 Week Lows - (Active).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
8				PRICE	%		NEWS
9							This one doesn't have any info.
10				$55.25	25.24%		He made $55.25 which is 25.24% more than last time.
11				$100.25	10.00%		He made 10% that last time which was $100.25.
12				$100.10	50.00%		$100.10 50%
13				$150.00	50.25%		50.25% $150.00
14				$55.25	100.00%		This one is bunched together$55.25 100%
Sheet2 (2)

 

[Rick Rothstein]

This function only looks for the special characters $ and % and assumes the number is always before the % sign and after the $ sign so there is no need for your WhereLocated argument (so my function doesn't have it)...

Function NumberExtract(News As String, Character As String) As Variant
  Dim Arr() As String
  NumberExtract = ""
  Arr = Split(News, Character)
  If UBound(Arr) = 1 Then
    If Character = "$" Then
      NumberExtract = Val(Split(Arr(1))(0))
    Else
      NumberExtract = (Mid(Arr(0), InStrRev(Arr(0), " ") + 1)) / 100
    End If
  End If
End Function

 

[JEC]

One more variant

Function jec(cell As String, y As Boolean) As Variant
 If Right(cell, 1) = "." Then cell = Mid(cell, 1, Len(cell) - 1)
 jec = Replace(Replace(Filter(Filter(Split(Replace(Replace(cell, "$", " $"), "%", "%$")), "$", 1), "%", y)(0), "$", ""), "%", "") / IIf(y, 100, 1)
End Function

=jec($G10,COLUMN(A1)-1)

Pul this one to the right if you want to get percentages

 

[OilEconomist]

This function only looks for the special characters $ and % and assumes the number is always before the % sign and after the $ sign so there is no need for your WhereLocated argument (so my function doesn't have it)...

Function NumberExtract(News As String, Character As String) As Variant
  Dim Arr() As String
  NumberExtract = ""
  Arr = Split(News, Character)
  If UBound(Arr) = 1 Then
    If Character = "$" Then
      NumberExtract = Val(Split(Arr(1))(0))
    Else
      NumberExtract = (Mid(Arr(0), InStrRev(Arr(0), " ") + 1)) / 100
    End If
  End If
End Function

Thanks @Rick Rothstein for the code. The reason I set it up the way I did is because I wanted it to be generic for future use as the special characters $ and % can change and is not always before or after respectively.

This works for the most part but how do you resolve the issue for something like "He made 20% more at $10. That was great as 20% is big increase." It does not pick up the 20%.

 

[OilEconomist]

One more variant

Function jec(cell As String, y As Boolean) As Variant
 If Right(cell, 1) = "." Then cell = Mid(cell, 1, Len(cell) - 1)
 jec = Replace(Replace(Filter(Filter(Split(Replace(Replace(cell, "$", " $"), "%", "%$")), "$", 1), "%", y)(0), "$", ""), "%", "") / IIf(y, 100, 1)
End Function

=jec($G10,COLUMN(A1)-1)

Pul this one to the right if you want to get percentages

Thanks @JEC for your response. I was using Rick's but it fails on two counts (1) if it had the special character more than once (2) if it was was something besides numbers.
Could you modify your function to be generic to where if I specify a character it will either get the first word or number before or after the character where "WhereLocated" would give that indication.
The special character will not always $ or %, but if made generic where I specify those characters and whether the special character is before or after the word or number to be extracted. Also if it is 75%, I can always do the math in the sub if needed.

Function jec(News, CharacterSpecial As Variant, WhereLocated As String) As Variant

    If WhereLocated = "Before" Then
        'Extract either the number or word before it (e.g. "It is $5.75 please" gives 5.75" or "I hate #Mondays anyway" will give Mondays)
         jec = 
    ElseIf WhereLocated = "After" Then
        'Extract either the number or word after it (e.g. "It is 75% okay" will give 75 or "I hate Mondays# anyway" will give Mondays)
        jec = 
    End If

End Function

 

[Rick Rothstein]

This works for the most part but how do you resolve the issue for something like "He made 20% more at $10. That was great as 20% is big increase." It does not pick up the 20%.

Firstly, none of your examples included multiple numbers for one of the symbols, so I'm not sure how you thought we would be able to figure out that there could be. Secondly, now that you have told us there could be multiple values, you haven't told us how we are to present them back to you... comma separated in one cell? each on its owh line? some other way?

 

[OilEconomist]

Firstly, none of your examples included multiple numbers for one of the symbols, so I'm not sure how you thought we would be able to figure out that there could be. Secondly, now that you have told us there could be multiple values, you haven't told us how we are to present them back to you... comma separated in one cell? each on its owh line? some other way?

Thanks @Rick Rothstein for your response. First I cannot think of all the permutations before they happened (i.e. I did know I would have two % numbers in the same line, the new data set after I submitted my request here had it), but figured it would extract at least one of the values.

Second, I thought since it was a string value, it would obtain both numbers and words.

If I give you a table with examples and final result that I am seeking, would you be able to work on it?

Thanks for your help on this and your contributions to this platform!

 

[Rick Rothstein]

If I give you a table with examples and final result that I am seeking, would you be able to work on it?

Yes, that would be most helpful.