How to clean a quantity field containing both values and units (different units) to have values only - all of which correspond to the same unit.
You have the following data pertaining to liquid volume:
However, the above layout is not ideal. Subsequently, you want to transform the data so that the format looks something like this:
Most of the units presented in the original data are in milliliters (mL), so it makes sense to convert the one value in Liters (L) to mL. Since this requires a conversion, you know that a calculation field is needed.
In order to do this, you need to do the following (you'll soon understand why):
- Duplicate Field
- Clean - Remove Letters
- Change Data Type to Number
- Rename Field (in this case Quantity-#)
Now you can create the following calculated field:
Where the CONTAINS function requires a string and substring and the reason for the previous steps becomes clear (multiplication).
Resulting in:
