On Sun, 11 Jan 2009 21:13:00 -0800, Gene Shackman <Gene
wrote:

I have a file with lines like these

nominally Roman Catholic 92% (less than 20% practicing)
Armenian Apostolic 94.7%
Roman Catholic 80.8%
Eastern Orthodox 80%
Roman Catholic 75%
Buddhist 9.3%
Baptist 42%

my file has 250 lines. I want to separate the data from the text. Is there
any way to do this other than manually going through each line and separating
the data? I considered a macro but each line is different. In some cases
the number has 2 digits and no decimals but in other cases it has decimals.

thanks



Not sure exactly what you mean.

The following macro will
operate on a range of selected cells
clear a few cells adjacent to the selected cells
place the numeric values into the adjacent cells (including % signs)

The values are extracted as text strings.

==================================
Option Explicit
Sub GetData()
Dim c As Range
Dim i As Long
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[\-+]?\b\d*\.?\d+\b%?"

For Each c In Selection
Range(c(1, 2), c(1, 3)).ClearContents 'clear right of data
If re.test(c.Value) = True Then
Set mc = re.Execute(c.Value)
For i = 0 To mc.Count - 1
c(1, i + 2).Value = mc(i)

Next i
End If
Next c
End Sub
======================================

If you want to extract these as numbers, and divide by 100 if the % sign is
present, then use this:

===========================
Option Explicit
Sub GetData()
Dim c As Range
Dim i As Long
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[\-+]?\b\d*\.?\d+\b%?"

For Each c In Selection
Range(c(1, 2), c(1, 3)).ClearContents 'clear right of data
If re.test(c.Value) = True Then
Set mc = re.Execute(c.Value)
For i = 0 To mc.Count - 1
If Right(mc(i), 1) = "%" Then
c(1, i + 2).Value = Left(mc(i), Len(mc(i)) - 1) / 100
Else
c(1, i + 2).Value = CDbl(mc(i))
End If
Next i
End If
Next c
End Sub
==================================

--ron