Formula for keeping "3.99" from values with "4.99 (3.99 CMP)" ?

[texhorn92]

Hi,

I am working with numerous columns with values such as "4.99 (2.99 CMP)", "3.99 (2.99 CMP)", "2.99 (1.99 CMP)"

I am trying to come up with a formula that keeps the information in the parenthesis, excluding the " CMP".

For example, if we have the following info: 4.99 (3.99 CMP)

I want to keep: 3.99

The way I have been approaching this is using a mid formula that keeps the information in the parenthesis, (3.99 CMP) and then perform a search/replace to remove " CMP", which gives me the 3.99. Is there a formula that makes this more efficient and eliminates the need for using search/replace?

Thanks!

 

[Eric W]

Welcome to the forum.

I don't know what your formula looks like, but the shortest I could come up with looks like this:

=-SUBSTITUTE(MID(A1,FIND(" ",A1),99),"CMP","")

 

[FDibbins]

If the value will always be x.xx, you could also use
=MID(A15,FIND("(",A15)+1,4)
If you need the answer to b a value
=--MID(A15,FIND("(",A15)+1,4)

 

[inactiveuserps07]

Changing FDibbins' formula slightly would catch everything up to xx.xx (assuming there is always a space before "CMP"):

=--MID(A15,FIND("(",A15)+1,5)

 

[FDibbins]

Changing FDibbins' formula slightly would catch everything up to xx.xx (assuming there is always a space before "CMP"):

=--MID(A15,FIND("(",A15)+1,5)

haha love it, good change, Eric