New user trying to convert values of a column of cells based on text of cells

Hello everyone, I apologize if this is not the right place to put this question. I am very new to this application, and don’t really know where to start in terms of documentation or what to try. I am working on a game, and wish to convert all numbers to cells with “gp” in them with a simple equation, let’s say, multiply the values by 0.2.

How does one convert selected data based on a particular string within the data? Thank you for your help and insight!

Ex.: (yes it’s D&D)
|Spellbook|50 gp|3 lb.|
|Spikes, iron (10)|1 gp|5 lb.|
|Spyglass|1,000 gp|1 lb.|
|Tent, two-person|2 gp|20 lb.|
|Tinderbox|5 sp|1 lb.|
|Torch|1 cp|1 lb.|
|Vial|1 gp|—|
|Waterskin|2 sp|5 lb.|
|Whetstone|1 cp|1 lb|

This depends on your actual data. There are 2 possibilities:

  • Your cells contain text then you may see 50 gp as '50 gp in the input box, after you click in a cell - then your problem is: You can’t calculate with text, so you have to convert text to number+unit first.
  • Or your cells contain real numbers and the unit is shown only as formatting. Then you have to find out wich elements contain the formatting “gp”.

I assume the first case and would suggest to split the data in the last 2
columns with “text-in-columns” to 4 columns using space " " as separator.
You need 2 empty columns to the right and it may be wise to try this with a copy of your file. After this you may use IF or filters to get the desired “gp”-rows.
Don’t mark the first column when using text-in-columns, as it seems you don’t wish to split this!