VBA extract numbers from string

[opislak]

Hello all,

I get e-mails from people who have to hand me numbers of man-days or man-hours. Is there a way using VBA to:
- put the string in a cell
- extract only the numbers from the strings I get
- yet include the plus-sign "+"
- replace the American-style dot with a comma
- keep the different numbers a part (not added)
- put the result back in the same cell

Example: if I get this string and put in in cell B1:
3MD+1.5md + 4.2 days
I would like to get this result, regardless of use of capitals or other ("MD" or "days" or "d" or whatever) and regardless of the use of a dot or a comma, again in cell B1:
= 3 + 1,5 + 4,2
of course, the result shown in the cell would be the addition of the numbers (8,7), which is fine, only in the formula bar, one would be able to see where this result comes from.
The use of TEXTJOIN doesn't give me what I want (it would show 31542)

Many thanks for your input.
Pat.

 

[DanteAmor]

Check if the following helps you.
One function is to obtain the equation. Another function to obtain the result of the sum.

Function extractnumbers(s As String)
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^1-9.+]"
    .Global = True
    extractnumbers = "=" & Replace(.Replace(s, ""), ".", ",")
  End With
End Function

Function sumnumbers(s As String)
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^1-9.+]"
    .Global = True
    sumnumbers = Evaluate("=" & .Replace(s, ""))
  End With
End Function

Dante Amor
	A	B	C	D
1		3MD+1.5md + 4.2 days	=3+1,5+4,2	8.7
2
Hoja2

Cell Formulas
Range	Formula
C1	C1	=extractnumbers(B1)
D1	D1	=sumnumbers(B1)

 

[DanteAmor]

One detail, I added the zero in the extraction:

Function extractnumbers(s As String)
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^0-9.+]"
    .Global = True
    extractnumbers = "=" & Replace(.Replace(s, ""), ".", ",")
  End With
End Function

Function sumnumbers(s As String)
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^0-9.+]"
    .Global = True
    sumnumbers = Evaluate("=" & .Replace(s, ""))
  End With
End Function

 

[opislak]

One detail, I added the zero in the extraction:

Function extractnumbers(s As String)
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^0-9.+]"
    .Global = True
    extractnumbers = "=" & Replace(.Replace(s, ""), ".", ",")
  End With
End Function

Function sumnumbers(s As String)
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^0-9.+]"
    .Global = True
    sumnumbers = Evaluate("=" & .Replace(s, ""))
  End With
End Function

This works fine, thank you very much. Have a nice day!

 

[Rick Rothstein]

For those who might be interested, here is an alternate way to sum the numbers in OP's text which does not call out to a Regular Expression engine...

Function SumNumbers(ByVal S As String)
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(s, X, 1) Like "[!0-9.+]" Then Mid(S, X) = " "
  Next
  SumNumbers = Evaluate(S)
End Function

 

[opislak]

For those who might be interested, here is an alternate way to sum the numbers in OP's text which does not call out to a Regular Expression engine...

Function SumNumbers(ByVal S As String)
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(s, X, 1) Like "[!0-9.+]" Then Mid(S, X) = " "
  Next
  SumNumbers = Evaluate(S)
End Function

Works fine too.

 

[DanteAmor]

This works fine, thank you very much. Have a nice day!

I'm glad to help you. Thanks for the feedback.