Calculate Dimmensions Where All Are In The Same Cell

Re: Extract Dimensions From Single Cell Entry

First, it seems you have this setup incorrectly because 12"x12"x12" in one cell IS NOT a number but a text string with numeric characters.

Assuming that these dimensions are length, width, height (or thickness), it would be easier to work with these values by setting them up into individual cells so that A1=12, B1=12, C1=12 (do NOT use the " character). Excel will "see" these entries as numbers which you can use in formulas.

Take time to read this very valuable page on Efficient Spreadsheet Design.

HTH,

AAE

Edit: if your example cell values accurately reflect what's really in the cell, then even the use of the MID function is out of the question due to the inconsistent way the data are entere - some with the quote characters, others without it.

Re: Extract Dimensions From Single Cell Entry

Assuming that Column A contains the data, try the following...

1) Select B1

2) Define the following...

Insert > Name > Define

Name: Values

Refers to:

=EVALUATE("{"&SUBSTITUTE(SUBSTITUTE(UPPER($A1),"""",""),"X",",")&"}")

Click Ok

Then try...

B1, copied down:

=PRODUCT(Values)

Hope this helps!

Re: Extract Dimensions From Single Cell Entry

How has that data got into excel? It might be a lot easier to import the data in a different way from how you're currently doing it so that the values are in separate cells to start with.
Anyway, if this is a one-off here's one way of going about it:
In cell B1 enter this formula:
=SUBSTITUTE(A1,CHAR(34),)
and copy it down as far as you need. It gets rid of the " characters, leaving:

Now select all the cells from column B and then press Ctrl+C (or via the menus:Edit|Copy)
Now select C1 and via the menus:Edit|PasteSpecial... , in the dialogue box check the 'Values' radio button, then OK. This will leave in column C the same as in column B but without the formula behind it.
Now select the cells in column C and via the menus:
Data|Text To Columns... , in the dialogue box choose 'Delimited', click 'Next'.
Here, check the 'Other' checkbox and put a lower case 'x' in the field next to it. You should see a preview. Just click 'Finish'.
You should now have the three values in columns C, D and E.
In cell F1, put this formula in to multiply them:
=E1*D1*C1
and copy down.

You could Copy and PasteSpecial Values the result which would mean you could delete all the intervening columns.

p45cal

Re: Extract Dimensions From Single Cell Entry

I can see this question has already been answered, but to post what I already wrote, if you want to do this the real man's way with a quick and dirty, nested loop procedure, then here you go:

Sub GetNumbersFromSelection()


    Dim rngCell         As Range
    Dim rngTarget       As Range
    Dim strVal          As String
    Dim astrSplit()     As String
    Dim i As Long
    
    For Each rngCell In Selection
        If Not IsEmpty(rngCell) Then
            strVal = rngCell.Value
            strVal = Replace(strVal, """", "")
            strVal = Replace(strVal, ".", ",")
            strVal = Replace(strVal, " ", "")
            
            astrSplit = Split(strVal, "x")
            
            Set rngTarget = rngCell.Offset(0, 1)
        
            For i = LBound(astrSplit) To UBound(astrSplit)
                rngTarget.Offset(0, i).Value = CDbl(astrSplit(i))
            Next i
        End If
    Next rngCell
    
End Sub

Right click the sheet containing your data, choose View code, and paste the above into the opened window. Then select the cells where your measuments are (in one column), and press Alt + F8 and run the macro GetNumbersFromSelection.

Re: Extract Dimensions From Single Cell Entry

This line

strVal = Replace(strVal, ".", ",")

caused a few problems in the English locale of Excel on my machine. (Or is that what makes it the real man's way!?:smile: )
p45cal

Re: Extract Dimensions From Single Cell Entry

p45cal - nice work. Do you by chance have a handy reference to the character codes? I'm looking for one.

AAE

Re: Extract Dimensions From Single Cell Entry

Quote from p45cal

This line

Code

strVal = Replace(strVal, ".", ",")

caused a few problems in the English locale of Excel on my machine. (Or is that what makes it the real man's way!?:smile: )
p45cal

Ah, well, the real man does what real man wants and screw the 80% majority or users, the most important thing is that it works for me!

Yeah, should probably think a little more when posting sometimes. Usually when the case is that I (or the OP in this case) needs something done as a one-time event, i.e. format garbage data, I just write the code out in two minutes and don't really bother to harness it with a multi-locale test scenarios

Thanks for pointing that out though.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Quote from AAE

p45cal - nice work. Do you by chance have a handy reference to the character codes? I'm looking for one.

The Chr function codes are really just the ASCII numbers for characters, 0 to 255.

The real man (again) writes this to Immediate window in VBE:

Workbooks.Add: For i = 0 To 255: Range("A" & i + 1).Value = i: Range("B" & i + 1).Value = Chr(i):Next i

... and he remembers the meanings of 9 and 13 by heart, because they're illegible in that output. The rest can refer to for instance:

http://www.petefreitag.com/cheatsheets/ascii-codes/

Re: Extract Dimensions From Single Cell Entry

You already have a few (handy references, that is).
1. Type the single character into cell A1, in cell B1 enter this formula:
=CODE(A1)
change the character in cell A1 for more results
2. Through the menus:Insert|Symbol... and in the dialogue box that shows up, select the character you want to know the code of, then look at the bottom of the dialogue box and the code is displayed there.
3. Make your own paper copy:
In cell A1 enter =ROW()
In cell B1 enter =CHAR(ROW())
Copy down to row 255
Copy PasteSpecial Values
Reorganise for your printer. (see attached)

p45cal

Re: Extract Dimensions From Single Cell Entry

Thanks Fencliff, p45cal.

I had forgotten about the ASCII code charts but should have remembered them from my prior MS-DOS batch file programming days.

There are still areas of Excel I haven't fully explored: the tip on Insert | Symbol gives me exactly the handy "pop up" chart I'm looking for. Although, for the benefit of others reading this post, I should point out that if you want the ASCII decimal code, as is referenced in your formula (=SUBSTITUTE(A1,CHAR(34),), you must be sure to choose this because it defaults to displaying unicode values.

Best Regards,

AAE

Re: Extract Dimensions From Single Cell Entry

JohnDrew, to echo the general consesus of the posters, Excel is a Spreadsheet Application yet those who use seem to insist on using it like a Word Processor!

Numbers and text should never be in the same cell and numbers should always be true numbers, not text.

What you should have is a table with row headings across row 1 and related data laid out underneath in a database format. Excel thrives on this format and allows you to use it how it was designed to be used.

Re: Extract Dimensions From Single Cell Entry

The text to columns feature will help convert your current strings into the prefered lay-out.