Using the substitution formula to calculate the average of two numbers

[mrichard]

Hi all,

I am trying to calculate the average of the following which is text in my excel worksheet:
Cell A1 has >=$1M and <$2M

I have used the substitution formula to remove the >= and < and $.

So now I am left with this:
1M and 2M

Is there a way to convert the 1M and 2M into 1,000,000 and 2,000,000 and take the average.
So it would equal 1,500,000.

Just thought somebody would have done this before.

Thanks for any help in advance

Mark

 

[Jeffrey Mahoney]

How about
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E17,">",""),"M",""),"<",""),"=",""))*1000000

 

[johnnyL]

With a UDF:

Function SumTextNumbers(TextString As String, Optional strDelim As String = " ") As Double
'
    Dim xNums As Variant, lngNum As Long
    
    xNums = Split(TextString, strDelim)
'
    For lngNum = LBound(xNums) To UBound(xNums)
        SumTextNumbers = SumTextNumbers + Val(xNums(lngNum))
    Next lngNum
End Function

And then this formula:

=SumTextNumbers(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,">",""),"=",""),"<",""),"$",""),"M","000000"),"and",""))/2

 

[johnnyL]

Here is a formula only approach:

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})/2*1000000

 

[JEC]

A UDF, which also works with number larger than 10. (>=$250M and <$21M)

Put this behind a module:

Function jec(cell As String) As Double
 With CreateObject("VBScript.RegExp")
   .Global = True
   .Pattern = "[^\d+\s]"
   jec = Evaluate(Replace(Application.Trim(.Replace(cell, "")), " ", "+")) / 2 * 1000000
 End With
End Function

Then call the function as

=jec(A1)

 

[Peter_SSs]

One example is not much to go on.

If your question has not been fully resolved by any of the above suggestions, can you clarify if all numbers are "M" numbers or might there also be "K" numbers or numbers with no immediately following letter?
Perhaps 5 to 10 varied examples of your data and the expected results might help clarify.