Separate dimensions is a string

[C Goody]

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

PRODUCT DIMENSIONS ARE 305X1070X1625MM I want to display in cell B2 C2 and D2 B2 305 C2 1070 D2 1625 <tbody> </tbody>

<tbody>
</tbody>

as you can see it is separating the sizes and ignoring the rest

so it shows this

DESCRIPTION	DEPTH	LENGTH	HEIGHT
PRODUCT DIMENSIONS ARE 305X1070X1625MM	305	1070	1625

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>
</tbody>

Hope thats makes sense and thanks all

 

[ParamRay]

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)

 

[Haluk]

Alternative:

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.

 

[C Goody]

Re: Separate dimensions in a string

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?

 

[C Goody]

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

 

[Rick Rothstein]

PRODUCT DIMENSIONS ARE 305X1070X1625MM I want to display in cell B2 C2 and D2 B2 305 C2 1070 D2 1625 <tbody> </tbody>

<tbody>
</tbody>

so it shows this

DESCRIPTION	DEPTH	LENGTH	HEIGHT
PRODUCT DIMENSIONS ARE 305X1070X1625MM	305	1070	1625

<tbody>
</tbody>

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?

 

[C Goody]

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?

PRODUCT	DIM1	DIM2	DIM3
STANDARD UNIT 305X1070MM
STANDARD UNIT 305X1070MM
STANDARD UNIT 305X1220MM
STANDARD UNIT 305X1220MM
STANDARD UNIT 305X760MM
STANDARD UNIT 305X915MM
STANDARD UNIT 305X915MM
STANDARD UNIT 355X1070MM
STANDARD UNIT 355X1070MM
STANDARD UNIT 355X1220MM
STANDARD UNIT 355X1220MM
MOBILE UNIT 460X1220X1770MM HIGH
MOBILE UNIT 460X915X1770MM HIGH
MOBILE UNIT 460X1220X1770MM HIGH
MOBILE UNIT 460X915X1770MM HIGH
SECURITY BOX 5 UNIT 460X1220X1770MM HIGH
SECURITY BOX 5 UNIT 460X915X1770MM HIGH
SECURITY BOX 3 UNIT 460X1220X1625MM HIGH
SECURITY BOX 3 UNIT 460X915X1625MM HIGH
SECURITY BOX 4 UNIT 460X1220X1625MM HIGH
SECURITY BOX 4 UNIT 460X915X1625MM HIGH

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Give this macro a try...

[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.

 

[C Goody]

Give this macro a try...]

Excellent thank you for that it worked perfect

 

[C Goody]

Hi Rick

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

MID FRAME 450 X 1500 X 1700MM as an example

<tbody>
</tbody>