Extract Numbers in strings

[albert211994]

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.

 

[Peter_SSs]

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)

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
	A	B
1	abc12334.12nvdv cdv5123.12ncdbcv234564.35	234564.35
2	2356	2356
3		N/A
4	gfdgfdh	N/A
5	asdfsaf8779fs1.2356985628df9sdf8f9fsdf9sd90f	8779
Sheet1

 

[Ingolf]

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.

 

[Ingolf]

Please ignore my post.

 

[Rick Rothstein]

Just for interest sake, here is another UDF (user defined function) that will also work...

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]

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)

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

	A	B
abc12334.12nvdv cdv5123.12ncdbcv234564.35
gfdgfdh
asdfsaf8779fs1.2356985628df9sdf8f9fsdf9sd90f

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]234564.35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]2356[/TD]
[TD="align: right"]2356[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]N/A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]N/A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]8779[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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]

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

 

[Rick Rothstein]

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]

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. )

 

[Rick Rothstein]

(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.