Strip out data using VBA
 

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

Strip out data using VBA
 

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

Strip out data using VBA
 

Here is a way with out using VBA, just a formula. Don't
know if you want this or not.

=MID(A2,FIND("L",A2,1),FIND("R",A2,1)-FIND("L",A2,1))

-----Original 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

.

Strip out data using VBA
 

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



.

Strip out data using VBA
 

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



.