I have a column containing text and numbers with operators, such as:
"4 weeks * 40 hours per week." or
"4 wks * 40 hrs per week = 160 hours." or
"2 wks * 40 hrs per week + 2 wks * 20 hrs per week."

I need to strip out the text and end up with a formula, such as "4*40",
resulting in 160.

Need to recognize +, -, *, /. And need to ignore anthing after =, if there
is one.

The objective is to take what is sometimes a long text string and check the
math in the string.

I'm guessing this will take some fancy code. Any help greatly appreciated.
--
CG

Try this UDF

Function GetResults(Target As String)

Results = ""
For i = 1 To Len(Target)
Select Case Mid(Target, i, 1)
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "*", "+", "/",
"-"
Results = Results & Mid(Target, i, 1)
End Select
Next i

GetResults = Evaluate(Results)
End Function


"CEG" wrote:

I have a column containing text and numbers with operators, such as:
"4 weeks * 40 hours per week." or
"4 wks * 40 hrs per week = 160 hours." or
"2 wks * 40 hrs per week + 2 wks * 20 hrs per week."

I need to strip out the text and end up with a formula, such as "4*40",
resulting in 160.

Need to recognize +, -, *, /. And need to ignore anthing after =, if there
is one.

The objective is to take what is sometimes a long text string and check the
math in the string.

I'm guessing this will take some fancy code. Any help greatly appreciated.
--
CG

This works well except when there is a result entered (i.e. 4 * 40 = 160).
If some goober enters 4 * 40 = 140, I want to be able to spot it without my
calculator.

Thanks for your help so far!!!
--
CG


"Joel" wrote:

Try this UDF

Function GetResults(Target As String)

Results = ""
For i = 1 To Len(Target)
Select Case Mid(Target, i, 1)
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "*", "+", "/",
"-"
Results = Results & Mid(Target, i, 1)
End Select
Next i

GetResults = Evaluate(Results)
End Function


"CEG" wrote:

I have a column containing text and numbers with operators, such as:
"4 weeks * 40 hours per week." or
"4 wks * 40 hrs per week = 160 hours." or
"2 wks * 40 hrs per week + 2 wks * 20 hrs per week."

I need to strip out the text and end up with a formula, such as "4*40",
resulting in 160.

Need to recognize +, -, *, /. And need to ignore anthing after =, if there
is one.

The objective is to take what is sometimes a long text string and check the
math in the string.

I'm guessing this will take some fancy code. Any help greatly appreciated.
--
CG

One more glitch. What if there are decimals in the numbers? As the formula
is now, .5 becomes 5...not good.
--
CG


"CEG" wrote:

This works well except when there is a result entered (i.e. 4 * 40 = 160).
If some goober enters 4 * 40 = 140, I want to be able to spot it without my
calculator.

Thanks for your help so far!!!
--
CG


"Joel" wrote:

Try this UDF

Function GetResults(Target As String)

Results = ""
For i = 1 To Len(Target)
Select Case Mid(Target, i, 1)
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "*", "+", "/",
"-"
Results = Results & Mid(Target, i, 1)
End Select
Next i

GetResults = Evaluate(Results)
End Function


"CEG" wrote:

I have a column containing text and numbers with operators, such as:
"4 weeks * 40 hours per week." or
"4 wks * 40 hrs per week = 160 hours." or
"2 wks * 40 hrs per week + 2 wks * 20 hrs per week."

I need to strip out the text and end up with a formula, such as "4*40",
resulting in 160.

Need to recognize +, -, *, /. And need to ignore anthing after =, if there
is one.

The objective is to take what is sometimes a long text string and check the
math in the string.

I'm guessing this will take some fancy code. Any help greatly appreciated.
--
CG

On Fri, 20 Feb 2009 09:57:02 -0800, CEG wrote:

I have a column containing text and numbers with operators, such as:
"4 weeks * 40 hours per week." or
"4 wks * 40 hrs per week = 160 hours." or
"2 wks * 40 hrs per week + 2 wks * 20 hrs per week."

I need to strip out the text and end up with a formula, such as "4*40",
resulting in 160.

Need to recognize +, -, *, /. And need to ignore anthing after =, if there
is one.

The objective is to take what is sometimes a long text string and check the
math in the string.

I'm guessing this will take some fancy code. Any help greatly appreciated.


==================
Option Explicit
Function foo(sStr)
Dim i As Long
Dim sTemp As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[^-\d+*/^=]"
sTemp = re.Replace(sStr, "")
re.Pattern = "=.*"
foo = Evaluate(re.Replace(sTemp, ""))
End Function
==========================
--ron

Thanks Ron. This does ignore what comes after the =, which is great, but I
still have the problem of numbers with decimals. See my last post before
this one.
--
CG


"Ron Rosenfeld" wrote:

On Fri, 20 Feb 2009 09:57:02 -0800, CEG wrote:

I have a column containing text and numbers with operators, such as:
"4 weeks * 40 hours per week." or
"4 wks * 40 hrs per week = 160 hours." or
"2 wks * 40 hrs per week + 2 wks * 20 hrs per week."

I need to strip out the text and end up with a formula, such as "4*40",
resulting in 160.

Need to recognize +, -, *, /. And need to ignore anthing after =, if there
is one.

The objective is to take what is sometimes a long text string and check the
math in the string.

I'm guessing this will take some fancy code. Any help greatly appreciated.


==================
Option Explicit
Function foo(sStr)
Dim i As Long
Dim sTemp As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[^-\d+*/^=]"
sTemp = re.Replace(sStr, "")
re.Pattern = "=.*"
foo = Evaluate(re.Replace(sTemp, ""))
End Function
==========================
--ron

I figured it out...added a decimal in the Pattern list. Thanks!!!!!
--
CG


"CEG" wrote:

Thanks Ron. This does ignore what comes after the =, which is great, but I
still have the problem of numbers with decimals. See my last post before
this one.
--
CG


"Ron Rosenfeld" wrote:

On Fri, 20 Feb 2009 09:57:02 -0800, CEG wrote:

I have a column containing text and numbers with operators, such as:
"4 weeks * 40 hours per week." or
"4 wks * 40 hrs per week = 160 hours." or
"2 wks * 40 hrs per week + 2 wks * 20 hrs per week."

I need to strip out the text and end up with a formula, such as "4*40",
resulting in 160.

Need to recognize +, -, *, /. And need to ignore anthing after =, if there
is one.

The objective is to take what is sometimes a long text string and check the
math in the string.

I'm guessing this will take some fancy code. Any help greatly appreciated.


==================
Option Explicit
Function foo(sStr)
Dim i As Long
Dim sTemp As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[^-\d+*/^=]"
sTemp = re.Replace(sStr, "")
re.Pattern = "=.*"
foo = Evaluate(re.Replace(sTemp, ""))
End Function
==========================
--ron

On Fri, 20 Feb 2009 13:37:01 -0800, CEG wrote:

I figured it out...added a decimal in the Pattern list. Thanks!!!!!
--
CG

Glad to help. Thanks for the feedback.

Adding a decimal to the pattern list will work so long as there are no decimals
that are part of the text to be eliminated.

If that might be the case, post some examples and I could rework the pattern.

Also, you should be aware that using the decimal within the pattern list (i.e.
[.] works; but if the decimal were to be used outside of the pattern list, it
has a different meaning (it would match any character in that circumstance).
--ron

I figured a workaround for the decimal vs period problem. However, I do have
one other issue€¦is there a way to extract any 7-digit numbers? Sometimes
there is a reference number used that does not belong in the calculation.
--
CG


"Ron Rosenfeld" wrote:

On Fri, 20 Feb 2009 13:37:01 -0800, CEG wrote:

I figured it out...added a decimal in the Pattern list. Thanks!!!!!
--
CG

Glad to help. Thanks for the feedback.

Adding a decimal to the pattern list will work so long as there are no decimals
that are part of the text to be eliminated.

If that might be the case, post some examples and I could rework the pattern.

Also, you should be aware that using the decimal within the pattern list (i.e.
[.] works; but if the decimal were to be used outside of the pattern list, it
has a different meaning (it would match any character in that circumstance).
--ron