Sounds like some data in column A which is not like the others.

Try the code with a workbook that has just one sheet.
If it still is a runaway, try removing half of the rows.
If it still is a runaway, try removing half again until you discover what
data is complicating it.


wrote in message
...
Rob:

I try to use this macro, but was in the loop, I can
not get out the program, it was stop in end if. any idea?

thanks for the help

Lillian
-----Original Message-----
Sub testit()
Dim wks As Worksheet, rng As Range, i As Long,
strTemp As String

For Each wks In Worksheets
For Each rng In Range(wks.Cells(1, 1), wks.Cells
(Rows.Count,
1).End(xlUp))
i = InStr(1, rng.Value, "L#")
If i 0 Then
strTemp = Mid(rng.Value, i)
i = 3: Do Until Not IsNumeric(Mid
(strTemp, i, 1)): i = i +
1: Loop
strTemp = Mid(strTemp, 1, i - 1)
MsgBox rng.Address(External:=True) &
vbNewLine & strTemp
End If
Next
Next
End Sub

"Lillian" wrote in
message
...
I have one excel spread sheet that have 4 sheets, which
is sheet1, sheet2, sheet3, sheet4, on each sheets of
columns A that has data like this "
C#1251934538L#0000000169R#00002P#00001", I need to
strip
out C#1251934538 and R#00002P#00001, all I need is
L#0000000129, another word is before L need to strip
out,
and after 9 from R need to strip out as well. how to
write the macro to do that, also this need to in the
loop
for sheet1, sheet2, sheet3, sheet4.

Simple word is the data I need is from L to last
numeric
no, before R. which is L#0000000129

thanks.


Lillian



.