Strip out data using VBA

  • Thread starter Thread starter Lillian
  • Start date Start date
L

Lillian

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
 
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
 
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))
 
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
 
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.
 
Back
Top