Strip Out Four Characters, then Average

[ryguy7272]

I posted this onto the end of a post that I put up earlier today. This is
tough and I can't tell if people stopped reading the old post or if no one
has been able to come up with a solution.

Basically, this is the scenario:
New request; just a bit different. I have this kind of setup in several
cells:
="(-30.5)"

This will eliminate the left parentheses:
=SUBSTITUTE(AD8,CHAR(40),"")

This gets rid of the right:
=SUBSTITUTE(AD8,CHAR(41),"")

Also, I have to get rid of the quotes!! Four characters need to be
stripped, and I want to calculate an average on top of that!! I know it's a
tall order. Can it be done?

I tried this, committed with CSE:
=AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"")

That didn't work, but even if it did, I would still need to strip out the
quotes somehow. Any ideas on this?

Thanks,
Ryan---

 

[Mike H]

Hi,

Untill someone solves this in a single formula you could extract your
nimbers using this then average them

=(IF(ISNUMBER(FIND("-",AD8)),"-","")&LOOKUP(10^23,--MID(AD8,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},AD8&"0123456789")),ROW(INDIRECT("1:"&LEN(AD8))))))*1

Mike

 

[Dave Peterson]

I'm confused.

Do you have this formula ="(-30.5)"
or do you have the actual text "(-30.5)" or even the text ="(-30.5)"

Maybe one of these--just keep adding more (not too many more!) =substitute()'s:

=AVERAGE(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AD8:AD12,"(",""),")",""),"""",""))
(still an array formula)

And I didn't include the another =substitute() to remove the = sign.

 

[Dave Peterson]

And you have another response at the other thread.

 

[ryguy7272]

Mike, yours calculated the Median. I needed the Mean and the Median. Dave,
your function worked perfect. I got the average, then got the median;
everything works great now!!

Thanks all!!
Ryan---

 

[ryguy7272]

Glenn, just saw yours. Your solution worked great too!
Thanks for everything!!
Ryan---

 

[Glenn]

I posted this onto the end of a post that I put up earlier today. This is
tough and I can't tell if people stopped reading the old post or if no one
has been able to come up with a solution.

Basically, this is the scenario:
New request; just a bit different. I have this kind of setup in several
cells:
="(-30.5)"

This will eliminate the left parentheses:
=SUBSTITUTE(AD8,CHAR(40),"")

This gets rid of the right:
=SUBSTITUTE(AD8,CHAR(41),"")

Also, I have to get rid of the quotes!! Four characters need to be
stripped, and I want to calculate an average on top of that!! I know it's a
tall order. Can it be done?

I tried this, committed with CSE:
=AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"")

That didn't work, but even if it did, I would still need to strip out the
quotes somehow. Any ideas on this?

Thanks,
Ryan---

Array formula (commit with CTRL+SHIFT+ENTER):

=AVERAGE(--SUBSTITUTE(SUBSTITUTE(AD8:AD12,")",""),"(",""))

 

[Ashish Mathur]

Hi,

You can use this formula to extract the numeric portion on the string (along
with the sign)

=1*MID(B9,MIN(SEARCH({"-";0;1;2;3;4;5;6;7;8;9},B9&"-0123456789",1)),SEARCH(")",B9,1)-MIN(SEARCH({"-";0;1;2;3;4;5;6;7;8;9},B9&"-0123456789",1)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com