So the code you need is refreshingly simple. I'll post that first, and then some details of what's going on in case you need to modify for later use.
The code:
The explanation:
So before we were using Left and Right for string manipulation. Those work great when
a)You know the exact length of the string you want to extract, and
b) the marker for what you want to extract (the R-Demo, RW, etc. or whatever it was) varies in length
But here, we know the marker is the same every time (the dash, "-"), and we (might) have a variable and unknown length. So we can use split, which is faster than a Left and a Right, and I think easier to look at.
How Split Works:
Let's take a look at the example from my code:
Cell.Value: what you want split up. In this case is every value in row I
"-": the string you want it to split at, what I was calling the marker. So in your first row, it will split Cell.Value into LJ452AV, SB39, and FR.
(0): Which one of those split you want to keep. (0) is, somewhat confusingly, the first split. If we had used (2), cell J1 would have been "FR"
Extra info that might come in handy:
There is also an optional value for how many times you want it to split the string. The default, a not necessary to include -1, means it will split every time it sees the marker. You can also tell it to split a set number of times regardless of how many markers there are. An example:
x = Split(Cell.Value, "-", 2)(0) would set x to:
LJ452AV
and
x = Split(Cell.Value, "-", 2)(1) would set x to:
SB39-FR
as we are saying only split twice the first time you encounter a dash.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks