Each of the numbers goes into separate columns.
I would start by selecting the column and doing a few edit|Replaces.
I'd replace all the space characters with |
Then all the X's with |
then all the /'s with |
And all the other separators that I could find with |
Then do data|text to columns
Delimited by / (and treat consecutive delimiters as one)
and finish up.
> I wonder if anyone can help me (again!) I have an excel sheet which has 500+
> rows of data which has come from a database - one of the colums is a size
[quoted text clipped - 20 lines]
>
> Wendy

Signature
Dave Peterson
WendyMc - 23 May 2008 14:38 GMT
Dave, many thanks for taking the time to reply to my posting, I have used the
Macro idea which worked a treat.
Thanks again
Wendy
> Each of the numbers goes into separate columns.
>
[quoted text clipped - 33 lines]
> >
> > Wendy
>I wonder if anyone can help me (again!) I have an excel sheet which has 500+
>rows of data which has come from a database - one of the colums is a size
[quoted text clipped - 20 lines]
>
>Wendy
Here is a macro that will parse out the numbers into separate columns. Please
look at it closely as there are two commented lines indicating whether the
numbers will be returned in "text" format, or in a numeric format. You did not
specify which you wanted.
Delete the line you don't want.
Numbers in text format will retain leading and trailing zeros, but may be more
difficult to use in other functions.
To enter this, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project explorer window, then Insert/Module and paste the
code below into the window that opens.
To use the macro, select the range you wish to split; then <alt-F8> opens the
Macro Dialog box. Select the Macro and <run>.
==========================================
Option Explicit
Sub SplitMeasurements()
Dim re As Object, m As Object, mc As Object
Dim c As Range
Dim i As Long
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d*\.?\d+"
re.Global = True
For Each c In Selection
If re.test(c.Text) = True Then
i = 0
Set mc = re.Execute(c.Text)
For Each m In mc
c.Offset(0, i).Value = m 'results in text
c.Offset(0, i).Value = CDbl(m) 'results in numbers
i = i + 1
Next m
End If
Next c
End Sub
==============================
--ron
WendyMc - 23 May 2008 14:22 GMT
I can't thank you enough for this Macro Ron - it worked a treat and just
saved me hours of work - many many thanks :-)
Wendy
> >I wonder if anyone can help me (again!) I have an excel sheet which has 500+
> >rows of data which has come from a database - one of the colums is a size
[quoted text clipped - 62 lines]
> ==============================
> --ron
Ron Rosenfeld - 23 May 2008 15:41 GMT
>I can't thank you enough for this Macro Ron - it worked a treat and just
>saved me hours of work - many many thanks :-)
>
>Wendy
Glad to help. Thanks for the feedback.
--ron