Extract numbers - exact values?

[ljubo_gr]

Hello dear MrExcel's

How to extract value from right, from an aray of data?

CMR: 30.501 litres, rest 11.000 for Barack Obama.

Cmr:31.700L but 5000 goes from R-7 to John Kerry.

cmr: 30000 L rest goes 500 to WhiteHouse from R-7

<tbody>
</tbody>

tHESE are only three examples, three rows of data, they are different but simmilar, How to extract 11000 5000 and 500 and Sum that?
This is impossible 'cause they are different, one start with "rest 11000", second starts with "but", third with "goes"....If i extract numbers i get 3050111000 3170050007......
Any help would be kindly appreciated, Thanks!

 

[ljubo_gr]

When there isn't R(reservoir signature) then search for "my number". He is always to left, behind CMR number, always smaller then CMR number. Thanks for your help.

 

[Rick Rothstein]

When there isn't R(reservoir signature) then search for "my number". He is always to left, behind CMR number, always smaller then CMR number. Thanks for your help.

Okay, I think this UDF (user defined function) will do what you want...

Function LastNumber(S As String) As Variant
  Dim X As Long, Words() As String
  Words = Split(S)
  LastNumber = ""
  For X = UBound(Words) To 0 Step -1
    If Left(UCase(Words(X)), 2) = "R-" Then
      LastNumber = ""
      Exit Function
    ElseIf Val(Words(X)) <> 0 Then
      LastNumber = Val(Words(X))
      Exit Function
    End If
  Next

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LastNumber just like it was a built-in Excel function. For example,

=LastNumber(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[ljubo_gr]

What if: "R-" or "R" someone misspelled reservoir designation R7 R-7, how to mod that udf code?

 

[Rick Rothstein]

What if: "R-" or "R" someone misspelled reservoir designation R7 R-7, how to mod that udf code?

Try it like this then...

Function LastNumber(S As String) As Variant
  Dim X As Long, Words() As String
  Words = Split(S)
  LastNumber = ""
  For X = UBound(Words) To 0 Step -1
    If Left(UCase(Words(X)), 2) Like "R[0-9-]" Then
      LastNumber = ""
      Exit Function
    ElseIf Val(Words(X)) <> 0 Then
      LastNumber = Val(Words(X))
      Exit Function
    End If
  Next

 

[ljubo_gr]

Reservoir designations are 5 to 12 so then "R[5-12-]" right?

 

[Rick Rothstein]

Reservoir designations are 5 to 12 so then "R[5-12-]" right?

No, the Like operator does not work that way. If there are other reservoir designations that you do not want to do this for, then I will have to change my code somewhat. I'll be back shortly.

 

[Rick Rothstein]

No, the Like operator does not work that way. If there are other reservoir designations that you do not want to do this for, then I will have to change my code somewhat. I'll be back shortly.

Okay, the following code will return nothing ("") only if the reservoir designation number is between (and including) 5 through 12 otherwise it will return the last number if there is one...

[table="width: 500"]
[tr]
	[td]Function LastNumber(S As String) As Variant
  Dim X As Long, Num As Long, Words() As String
  Words = Split(S)
  LastNumber = ""
  For X = UBound(Words) To 0 Step -1
    If Replace(Words(X), "-", "") & " " Like "[Rr][5-9][!0-9]*" Or Replace(Words(X), "-", "") Like "[Rr]1[0-2]*" Then
      LastNumber = ""
      Exit Function
    ElseIf Val(Words(X)) <> 0 Then
      LastNumber = Val(Words(X))
      Exit Function
    End If
  Next
End Function[/td]
[/tr]
[/table]

 

[ljubo_gr]

CMR:32.008L ostatak 10.000 za Zagreb >>>>here udf not working. Peter+s formula Working! CMR: 32199L, ostatak 5200L u R-12! >>>>here udf Working! Peter's formula not working.But Peters formula is working if I put (!) at the end. <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

CMR:32.008L ostatak 10.000 za Zagreb >>>>here udf not working. Peter+s formula Working! <tbody>

<tbody>

Just saying something doesn't work is not helpful... you have to tell us what it being returned AND what you expected to be returned. Based on the difference between Peter's formula and my UDF, it appears that your decimal point is a comma and your thousands separator is a dot. If that is the case, see if this revised UDF works for you... [table="width: 500"] [tr] [td]Function LastNumber(S As String) As Variant Dim X As Long, Num As Long, Words() As String Words = Split(S) LastNumber = "" For X = UBound(Words) To 0 Step -1 Words(X) = Replace(Replace(Words(X), ".", ""), ",", ".") If Replace(Words(X), "-", "") & " " Like "[Rr][5-9][!0-9]*" Or Replace(Words(X), "-", "") Like "[Rr]1[0-2]*" Then LastNumber = "" Exit Function ElseIf Val(Words(X)) <> 0 Then LastNumber = Val(Words(X)) Exit Function End If Next End Function[/td] [/tr] [/table]

 

[ljubo_gr]

UDF not working, thank you Rick, God bless you.
I'll use Peter_Ss Extract(2) formula from post#11, I'll search database for R- number and add (!) to the end. THANK YOU ALL FOR YOUR TIME! Please, moderator, if it's necessary rename this thread to some appropriate.....