Separate dimensions is a string

C Goody

New Member
Joined
Oct 11, 2013
Messages
13
Hi All

Can someone help me out please I am am trying to create a formula to separate dimensions in a cell and display them in separate cells

example in Cell A2 it has

[TABLE="width: 360"]
<tbody>[TR]
[TD="width: 360"]PRODUCT DIMENSIONS ARE 305X1070X1625MM

I want to display in cell B2 C2 and D2

[TABLE="width: 217"]
<tbody>[TR]
[TD="width: 89, align: right"]B2
305[/TD]
[TD="width: 64, align: right"]C2
1070[/TD]
[TD="width: 64, align: right"]D2
1625
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
as you can see it is separating the sizes and ignoring the rest

so it shows this
[TABLE="width: 577"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]DESCRIPTION[/TD]
[TD="align: center"]DEPTH[/TD]
[TD="align: center"]LENGTH[/TD]
[TD="align: center"]HEIGHT[/TD]
[/TR]
[TR]
[TD="align: center"]PRODUCT DIMENSIONS ARE 305X1070X1625MM[/TD]
[TD="align: center"]305[/TD]
[TD="align: center"]1070[/TD]
[TD="align: center"]1625[/TD]
[/TR]
</tbody>[/TABLE]


Hope thats makes sense and thanks all
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Code:
b2: = left(a2, find("x", a2) - 1)
c2: = mid(a2, find("x", a2) + 1, find("x", a2, find("x", a2) + 1) - find("x", a2) - 1)
d2: = mid(a2, find("x", a2, find("x", a2) + 1) + 1, find("mm", a2) - find("x", a2, find("x", a2) + 1) - 1)
 
Upvote 0
Alternative:

Code:
B2:  =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2;"PRODUCT DIMENSIONS ARE";"");"X";REPT(" ";100));1;100))
C2:  =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2;"PRODUCT DIMENSIONS ARE";"");"X";REPT(" ";100));100;100))
D2:  =SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2;"PRODUCT DIMENSIONS ARE";"");"X";REPT(" ";100));200;100));"MM";"")

Replace semicolons ( ; ) by commas ( , ) according to your version of Excel.
 
Last edited:
Upvote 0
Re: Separate dimensions in a string

Code:
b2: = left(a2, find("x", a2) - 1)
c2: = mid(a2, find("x", a2) + 1, find("x", a2, find("x", a2) + 1) - find("x", a2) - 1)
d2: = mid(a2, find("x", a2, find("x", a2) + 1) + 1, find("mm", a2) - find("x", a2, find("x", a2) + 1) - 1)

Thanks for that, but I couldn't get this to work, the first line for B2 returns "PRODUCT DIMENSIONS ARE 305" Have I got this wrong?
 
Upvote 0
Thanks Haluk, however there is a problem say in column A I have different product names or codes before the first dimension of 305, how can I formulate column B so I can simply drag it down column B with ease

thanks
 
Upvote 0
[TABLE="width: 360"]
<tbody>[TR]
[TD="width: 360"]PRODUCT DIMENSIONS ARE 305X1070X1625MM

I want to display in cell B2 C2 and D2

[TABLE="width: 217"]
<tbody>[TR]
[TD="width: 89, align: right"]B2
305[/TD]
[TD="width: 64, align: right"]C2
1070[/TD]
[TD="width: 64, align: right"]D2
1625[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

so it shows this
[TABLE="width: 577"]
<tbody>[TR]
[TD="align: center"]DESCRIPTION[/TD]
[TD="align: center"]DEPTH[/TD]
[TD="align: center"]LENGTH[/TD]
[TD="align: center"]HEIGHT[/TD]
[/TR]
[TR]
[TD="align: center"]PRODUCT DIMENSIONS ARE 305X1070X1625MM[/TD]
[TD="align: center"]305[/TD]
[TD="align: center"]1070[/TD]
[TD="align: center"]1625[/TD]
[/TR]
</tbody>[/TABLE]
One example does not always provide the information we need. Is all of your cells structured the same way... the text "PRODUCT DIMENSIONS ARE " followed by 3 numbers delimited by X's and ending with the letters MM? If not, can you give us an idea how the various rows of values vary?
 
Upvote 0
One example does not always provide the information we need. Is all of your cells structured the same way... the text "PRODUCT DIMENSIONS ARE " followed by 3 numbers delimited by X's and ending with the letters MM? If not, can you give us an idea how the various rows of values vary?

[TABLE="width: 748"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]PRODUCT[/TD]
[TD]DIM1[/TD]
[TD]DIM2[/TD]
[TD]DIM3[/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 305X1070MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 305X1070MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 305X1220MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 305X1220MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 305X760MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 305X915MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 305X915MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 355X1070MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 355X1070MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 355X1220MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]STANDARD UNIT 355X1220MM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MOBILE UNIT 460X1220X1770MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MOBILE UNIT 460X915X1770MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MOBILE UNIT 460X1220X1770MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MOBILE UNIT 460X915X1770MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SECURITY BOX 5 UNIT 460X1220X1770MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SECURITY BOX 5 UNIT 460X915X1770MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SECURITY BOX 3 UNIT 460X1220X1625MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SECURITY BOX 3 UNIT 460X915X1625MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SECURITY BOX 4 UNIT 460X1220X1625MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SECURITY BOX 4 UNIT 460X915X1625MM HIGH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Give this macro a try...
Code:
[table="width: 500"]
[tr]
	[td]Sub GetDimensions()
  Dim R As Long, X As Long, Data As Variant, Dimensions As Variant, Parts As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  ReDim Dimensions(1 To UBound(Data), 1 To 3)
  On Error Resume Next
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X) Like "#[Xx]#*" Then
        Parts = Split(Trim(Mid(Data(R, 1), InStrRev(Data(R, 1), " ", X))), "x", , vbTextCompare)
        Dimensions(R, 1) = Parts(0)
        Dimensions(R, 2) = Val(Parts(1))
        Dimensions(R, 3) = Val(Parts(2))
      End If
    Next
  Next
  On Error GoTo 0
  Range("B2").Resize(UBound(Dimensions), 3) = Dimensions
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (GetDimensions) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:
Upvote 0
Hi Rick

can you add some code in where there is occasionally a space before and after the "x"

[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 448"]MID FRAME 450 X 1500 X 1700MM

as an example [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top