Need strip out data from column A using VBA

L

Lillian

have one excel spread sheetthat have 4 sheets, which
is sheet1, sheet2, sheet3, sheet4, ech sheets has about
3000 recores on each sheets of columns A that has data
example like
"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.
 
P

Patrick Molloy

add the following code to a standard module

Option Explicit
Sub ReplaceText()
Dim index As Long

For index = 1 To 4

With Worksheets("sheet" & index)

.Columns(2).Insert

With .Range(.Range("B1"), .Range("A1").End(xlDown).Offset(0, 1))

.FormulaR1C1 = "=stripper(RC1)"
.Value = .Value

End With

.Columns(1).Delete

End With

Next

End Sub


Function stripper(text As String)
Dim posL As Long
Dim posR As Long

posL = InStr(text, "L")
posR = InStr(text, "R")

stripper = Mid(text, posL, posR - posL)

End Function



Run the procedure ( or "macro") called ReplaceText
method: the code inserts a new column "B", places a foumula in B using
the user defined function "stripper()" also given in the above code.
When the formula is placed into the cell, it is eveluated automatically, and
the code then converts the formula to a value. Finally the original column
A is deleted leaving your new data as A
 
J

J.E. McGimpsey

one way:

Public Sub StripAllButLNumbers()
Dim wkSht As Worksheet
Dim rCell As Range
On Error Resume Next
For Each wkSht In Sheets(Array( _
"Sheet1", "Sheet2", "Sheet3", "Sheet4"))
With wkSht
For Each rCell In .Range("A1:A" & _
.Range("A" & .Rows.Count).End(xlUp).Row)
With rCell
.Value = Mid(Left(.Text, _
InStr(.Text, "R") - 1), InStr(.Text, "L"))
End With
Next rCell
End With
Next wkSht
On Error GoTo 0
End Sub
 
I

igor

there is a function (in string) InSrt that will allow you
to look for the specific letter.

good luck
 
D

Don Guillett

try

Sub StripOut()
For Each c In Range("L2:L62")
x = InStr(1, c, "L")
y = InStr(1, c, "R")
c.Offset(0, 1) = Mid(c, x, y - x)
Next
End Sub
 
L

Lillian

J.E:

Your VBA work great, I got what I need it, but I do
not understant for the following:
With rCell
.Value = Mid(Left(.Text, _
InStr(.Text, "R") - 1), InStr(.Text, "L"))

Can you explain to me, thanks.

Lillian
 
J

J.E. McGimpsey

Breaking it out:

Instr(.Text, "R") finds the position of the first instance of the
letter R in the Text of the cell. Likewise Instr(.Text, "L") finds
the position of the first instance of the letter L.

For purposes of discussion, assume the first R is at position 30 and
the first L is at position 20.

Left(.Text, instr(.Text, "R") - 1) returns the first (30-1)=29
characters of the text, stripping off the right hand side.

Then Mid(Left(...),Instr(.Text, "L")) takes the result from above
and returns the text starting at position 20 and following,
stripping off the left hand side.
 
L

Lillian

Dear J.E:

Your explation was perfectly understanable, thank you
so much for all your help.

Wish you have a happy new year.

Lillian
 
Top