Need strip out data from column A using VBA

  • Thread starter Thread starter Lillian
  • Start date Start date
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.
 
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
 
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.
 
Dear J.E:

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

Wish you have a happy new year.

Lillian
 
Back
Top