Hi Gene,

The following code will separate the first percentage value into a separate
column and remove it from the first column. I have not handled the "(less
than 20% practicing)" because not sure what you want to do with it. Maybe it
is OK left in the original data but let me know.

Note the comments where you might have to edit the code to suit.

Ensure you back up your data before running the code in case it does not do
exactly what you expect.

Sub SeparatePercentages()
Dim rngToSeparate As Range
Dim cel As Range
Dim i As Long
Dim strChar As String
Dim strTemp As String

'Edit sheet name to suit your sheet name
With Sheets("Sheet1")
'Edit the following range to suit range of your data
Set rngToSeparate = .Range("A1:A7")
End With

'Edit following line to suit required column for results
'It must be the next column to the right of the original data
Columns("B:B").NumberFormat = "0.00%"


For Each cel In rngToSeparate
strTemp = ""
For i = 1 To Len(cel.Value)
strChar = Mid(cel.Value, i, 1)

Select Case strChar
Case 0 To 9, ".", "%"
strTemp = strTemp & strChar
If strChar = "%" Then Exit For
End Select
Next i

'Extract value only from variable without % sign
cel.Offset(0, 1) = Val(Left(strTemp, Len(strTemp) - 1)) / 100

'Delete the value and percentage sign from original data
cel.Value = Replace(cel.Value, " " & strTemp, "")
Next cel

End Sub


--
Regards,

OssieMac


"Gene Shackman" 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