albert211994

New Member
Joined
Feb 7, 2017
Messages
10
Hi to All,

Can I extract the highest numbers in a string? For example, I have a string as below.

abc12334.12nvdv cdv5123.12ncdbcv234564.35

I want to return the highest number value which is 234564.35.

Thank you in advance.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
One way would be to use a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Code:
Function GetMax(s As String) As Variant
  Dim m As Variant
  
  GetMax = "N/A"
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d+(\.\d*)?"
    For Each m In .Execute(s)
      If Val(m) > GetMax Or GetMax = "N/A" Then GetMax = Val(m)
    Next m
  End With
End Function

Excel Workbook
AB
1abc12334.12nvdv cdv5123.12ncdbcv234564.35234564.35
223562356
3N/A
4gfdgfdhN/A
5asdfsaf8779fs1.2356985628df9sdf8f9fsdf9sd90f8779
Sheet1
 

Ingolf

Banned
Joined
Mar 20, 2011
Messages
809
Hi,

May I ask you?

Our system uses "," (comma), not "." (dot) as decimal separator.
VBA code does not work correctly.
instead of - say 125,18 the code only recurs 125 (without decimals)
I've replaced in the VBA code "." (dot) with "," (comma), but after running the VBA code they only get the whole part of the number. The result is the same as the one above - that is, they get the number but no decimal places.
Can you change the code?

Thanks.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,715
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Just for interest sake, here is another UDF (user defined function) that will also work...
Code:
Function MaxInText(S As String) As Variant
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9.]" Then Mid(S, X) = " "
  Next
  MaxInText = Evaluate("MAX(" & Replace(S, " ", ",") & ")")
End Function
 

albert211994

New Member
Joined
Feb 7, 2017
Messages
10
One way would be to use a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Code:
Function GetMax(s As String) As Variant
  Dim m As Variant
  
  GetMax = "N/A"
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d+(\.\d*)?"
    For Each m In .Execute(s)
      If Val(m) > GetMax Or GetMax = "N/A" Then GetMax = Val(m)
    Next m
  End With
End Function

Sheet1

AB
1abc12334.12nvdv cdv5123.12ncdbcv234564.35234564.35
223562356
3N/A
4gfdgfdhN/A
5asdfsaf8779fs1.2356985628df9sdf8f9fsdf9sd90f8779

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=GetMax(A1)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Wow! This works fine. I just have to tweak it a little bit to get the desired result.
Thank you very much.
It helps a lot.
 

albert211994

New Member
Joined
Feb 7, 2017
Messages
10

ADVERTISEMENT

Wow! It works exactly as I wanted.
Thank you very much.
This helps a lot.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,715
Office Version
  1. 2010
Platform
  1. Windows
Wow! It works exactly as I wanted.
Thank you very much.
This helps a lot.
Not sure who you are replying to, but if it is me, I just wanted to point out that Peter posted code in Message #2 which also should work for you.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Wow! This works fine. I just have to tweak it a little bit to get the desired result.
Thank you very much.
It helps a lot.
You are very welcome. :)

(Rick, probably confusion because the post I have just quoted was caught in a moderation queue for a while & has only become visible after your post. :))
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,715
Office Version
  1. 2010
Platform
  1. Windows
(Rick, probably confusion because the post I have just quoted was caught in a moderation queue for a while & has
only become visible after your post. :))
You are right... that message wasn't there when I responded (I don't think I ever heard of a "moderation queue" before, although I think I can guess what it is). In that case, I would like to point out to the OP that the code I posted in Message #5 should also work for him.:LOL:
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,199
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top