Hi guys,

Apologies if something similar has been posted before, if had a hunt through some of the older posts but haven't spotted anything.

Basically my problem is that I have 2 kinds prices in a spreadsheet. A default one for standard products, additional option price which either adds or remove a value if the product comes in black or large etc.

My stores under a sale now so I need to remove 25% from all the prices and the additional option prices are causing me difficulties.

e.g.
Price:
321
3950
[ADD]759.6
[ADD]1520.4
[REMOVE]1.2

Its the words ADD and REMOVE that throwing me. For the first two defaults I imputed =CELL/100*75. But this doesn't work with the cells with words in them.

Any ideas? Thanks in advance
Amy

Hi Amy,

There are two solution. The easier but a bit manual is to use Text To Columns feature and delimiting it with the ']' character. This will take out the number after the ] character for your use. You will have to then merge the two columns to contain numbers.

If you insist on using the formula the following formula will give you the 75% of the available value.
=IF(ISNUMBER(A1),A1*0.75,(RIGHT(A1,LEN(A1)-FIND("]",A1,1)))*0.75)
This is assuming that your value is in A1.

Thank You,
Prashant