Forum Discussion
Calculate total Dimensions
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!
5 Replies
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
)- Thank you both for these great responses.
I wasnt clear enough in my original post, but im also trying to work out how to calculate cubic meters for the 'dimensions' coloumn and then total that as well?
Is that possible?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.
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.
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.
