1

I have a large column of prices, however, sometimes the word [FIXED] also appears in these columns (see screenshot below).

I need to convert this price list to wholesale. All the products wholesale prices are exactly 50% of the retail price. I recently discovered more advanced features in google sheets - even playing with some scripts to accomplish more hefty tasks like this one.

Another issue I have is some prices have a .99 for example: 59.99 retail. The wholesale price for this would be 30.00 however.

Is there something I can do to convert all the prices in a specific column by half? 50% of the original value?

Any help would be much appreciated. I know basic js.

enter image description here

Improve this question
3
  • You basically would use =MID() or =SUBSTITUTE() to narrow every item down to a numeric value since your text is always the same. Then simply apply your 50% formula and add the text back if desired.
    – Eric F
    Apr 4 '19 at 16:21
  • 50% is a pretty big mark up on clothes by the way!
    – Eric F
    Apr 4 '19 at 16:21
  • 1
    Good thing we arent selling clothes. xD
    – Patrick
    Apr 4 '19 at 16:32
1

Just put the below formula in cell B2 and copy it to all other cells.

=if(A2="", "",  iferror(left(A2, find("]", A2)), "") & round(right(A2, len(A2) - iferror(find("]", A2), 0)), 0)/2)
Improve this answer
0 

=ARRAYFORMULA(IFERROR(IFERROR(REGEXEXTRACT(A2:A, "\[FIXED\]"))&
 TEXT(ROUND(REGEXEXTRACT(TO_TEXT(A2:A), "\d+.\d+|\d+")*1-
 (REGEXEXTRACT(TO_TEXT(A2:A), "\d+.\d+|\d+")*1)*50%, 2),"#.00")))

0

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.