Sep 27 2021 07:39 PM
Hi,
I wonder if someone can help me. I export a spreadsheet for work that has a column containing dimensions.
While I'm at it, is there a way to calculate total weight as well? This one i normally can do but the "KG" text in the field is causing me some problems
Thanks in advance!
Sep 27 2021 11:33 PM
Select cell range.
right mousekey
"Format cells ..."
Numbers -> Category -> Click "Custom"
Type: Standard" KG"
OK.
You only need to enter the value and the value description is automatically displayed.
In the inserted file, A2: A7 is formatted in the same way.
I would be happy to know if I could help.
NikolinoDE
Was the answer useful? Mark them as helpful!
This will help all forum participants.
Sep 28 2021 01:25 AM
@JonesyT You could use Find & Replace to get rid of the " KG" part
Select the column(s) with the weights in them. Ctrl-H to open the Find & Replace window. Type " KG" (i.e. a space and capital K and G) in the Find field. Leave the Replace field blank. Replace All and close the window. Now you should be left with numbers that you can calculate with.
Sep 28 2021 02:05 AM
Sep 28 2021 02:39 AM - edited Sep 28 2021 02:43 AM
@JonesyT You could Text-to-columns from the Data ribbon to split the Dimension text string. Use space as the delimiter and you will end up with something like this:
Now you are all set to calculate volume from the numbers in columns with the numbers (in this case A, D and G).
Alternatively, go for a PowerQuery solution that you can use over and over again without having to split the dimensions column and enter formula and copy it down.
An example is included in the attached file.
Sep 28 2021 03:39 AM
Hi @JonesyT
There are different possibilities and I default to worksheet functions when reasonably possible;
Making the weights to number I'ld use
=NUMBERVALUE(SUBSTITUTE(A2;" KG";"");".")
where substitute is to get rid of KG.
If you want to show the number as kilo, use @NikolinoDE guide to format the numbers with trailing unit.
The volume is a longer formula. Cleans the data, make it look like HTML and parse it as XML to finally calculate the volume.
The LET function returns whatever is on the last line (I used alt+enter to make new lines).
You may test the different parts by changing the last output eg to data or dataArrayNum
=LET(range;C2;
data;SUBSTITUTE(range;" CM";"");
data_as_html;"<tr><td>" & SUBSTITUTE(data;" X ";"</td><td>") & "</td></tr>";
dataArrayText;transpose(FILTERXML(data_as_html;"//td"));
dataArrayNum;NUMBERVALUE(dataArrayText;".");
output;PRODUCT(dataArrayNum);
output
)