Separate dimensions is a string

C

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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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

ParamRay said:
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)
Click to expand...

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
C Goody said:
[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]
Click to expand...
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
Rick Rothstein said:
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?
Click to expand...

[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
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
646
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
605
Peter_SSs
Peter_SSs
J
  • Question Question
Scraping data from WIFI networks that are available
10 11 12
Replies
110
Views
8K
yereverluvinunclebert
Y
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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