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.

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




--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Lillian" wrote in message
...
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.

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


In article ,
"Lillian" wrote:

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.

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


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


In article ,
"Lillian" wrote:

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.

.

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.

In article ,
"Lillian" wrote:

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.

Dear J.E:

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

Wish you have a happy new year.

Lillian
-----Original Message-----
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.

In article ,
"Lillian" wrote:

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

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

good luck

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

.

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
--
Don Guillett
SalesAid Software

"Lillian" wrote in message
...
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.