Advanced Text to Columns??

  • Thread starter Thread starter WendyMc
  • Start date Start date
W

WendyMc

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
column and has data like the bit below:

3.803 X 3.327 X 0.391
6.519 X 6.053 X 0.27
003.745X 002.080X 009.850
003.745X 002.080X 009.850
002.790X 001.580X 006.400
002.860X 001.580X 009.470
3.05 / 2.075 / 2.05
2.68 X 2.0 X 9.53
003.660X 002.230X 004.640

I would like to split this into 3 seperate colunms and thought I had nothing
better to do but use the text to colums comand - however there are so many
different formats that even with the column sorted it is taking me a long
time to sort out!

Is there an easier way to do this??

Many thanks

Wendy
 
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
column and has data like the bit below:

3.803 X 3.327 X 0.391
6.519 X 6.053 X 0.27
003.745X 002.080X 009.850
003.745X 002.080X 009.850
002.790X 001.580X 006.400
002.860X 001.580X 009.470
3.05 / 2.075 / 2.05
2.68 X 2.0 X 9.53
003.660X 002.230X 004.640

I would like to split this into 3 seperate colunms and thought I had nothing
better to do but use the text to colums comand - however there are so many
different formats that even with the column sorted it is taking me a long
time to sort out!

Is there an easier way to do this??

Many thanks

Wendy
Click to expand...

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
 
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
 
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
 
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
Click to expand...

Glad to help. Thanks for the feedback.
--ron
 
Back
Top