in Microsoft Excel, how can I create formula for CBM assuming in a cell there is a dimension L x W/B x H?

Question

I know how to calculate the CBM which is L x W/D x H (in cm). I have a cell in Dimension column and I want the CBM column to automatically retrieve the info based on the Dimension input. I don't want to have 3 separate columns for Length, Width/Depth and Height.
Also assuming a product has two parts. Each part has its own dimension and this is indicated in one cell in Dimension column. What is the formula to calculate both dimensions and have Excel retrieve the CBM.

Answer 1

I understand what you want but I don't believe it will be the most efficient way of doing this. You're much better off having your dimensions placed in individual cells. It is the most efficient way to perform calculations on the dimensions.

That said, I wrote you a formula that works. However, the delimiter must be lower-case letter "x". If your dimensions are separated by any other characters it will not work. You'll need to change the delimiter in the formula or reference it to another cell containing the delimiter.

Here is the formula that refers to text contained in cell A1:

=(VALUE(LEFT(A1, FIND("x", A1, 1)-1)))*(IF(ISERROR(VALUE(MID(A1, FIND("x", A1, 1)+1, LEN(A1)-FIND("x", A1, FIND("x", A1, 1)+1)))), VALUE(MID(A1, FIND("x", A1, 1)+1, (LEN(A1)-1)-FIND("x", A1, FIND("x", A1, 1)+1))), VALUE(MID(A1, FIND("x", A1, 1)+1, LEN(A1)-FIND("x", A1, FIND("x", A1, 1)+1)))))*(VALUE(MID(A1, FIND("x", A1, FIND("x", A1, 1)+1)+1, 50)))

I tested it with these text strings and it worked perfectly:

3x4x7
3 x 4 x 7
12x3x12
12 x 3 x 11
333x12x66
4 x 3 x16
199 x 213 x 664

By the way, you should investigate "Text to Columns" feature in the Data pull down. It is simple to use.

Hope this helps!
Kind regards,
QwertyKPH @ Yahoo

Answer 2

1. Start Microsoft Excel, and open the file you want to use. 2. Click on the cell in which you want the total to appear. 3. Press the + key on the keyboard. This character will appear in the cell and in the Formula Bar. 4. Click on the first cell you want to add. That cell's name, such as B6, appears in the Formula Bar after the + sign. 5. Press the + key again. 6. Click on the second cell you want to add. That cell's name appears in the Formula Bar. 7. Press Enter to complete the equation. The total will appear in the initial cell. - Hope i helped :)

Answer 3

If you don't want to write in 3 columns, the easiest way is to type
=LXBXH (lenght, breadth, height respectively) in column A for example.
This will get you the CBM.
To see its constituents, you'll have to create a user define forumla. See link below.