Hello everybody,



I have a list (starting from A1) which items are separated by "/".

This list is 50 lines long. There are many items but I need to extract only the characters between the first 2 "/". What comes before and what is after can be deleted.

Sometimes it can happen that there is only 1 "/" followed by the letter "N". In this case I don't need to extract anything as N = Nothing.

Also I would like to avoid doubles.

Is there a way to create a macro that can do this?

Thank you all

Hi, Barry. As always in programming, there's more than one way to do this. You can do it manually with the InStr function. You can use the Split function (good more more than just space-delimited words). You can also use Regular Expressions, which is an advanced topic but can be very useful when your pattern-recognition needs are complex.

Let me ask about a couple of sample cases:

"123/456/789/abc/def": You want to extract "456".
"Now is the/time for all/good men...///": You want "time for all".
"$4,213.99/N": You want "".

But what about these:
"///": ""?
"tuhe/N/thueat": ""?
"$4,213.99/": ""? The same whether there's an 'N' or not, in other words?

If I'm guessing right in the above three cases, then it seems to me the simplest way to do this is to use the Split function, like this:

' As the code begins, StrVal = "something/something/something..."
arr = Split(StrVal, "/") 'creates an array starting with arr(0)
Value2 = arr(1) 'the second value
If Value2 = "N" then Value2 = ""

If you see a problem with any of my test questions, then maybe some other logic would have to be used.

/* Standard warning: I haven't tested this logic, I'm just spinning it up off the top of my head. Test before trusting. */

Hello Bob,

here is a true example:

-CL/N

In this case I don't want to have anything

-DR/PMC00263ZP/FRU/2574/C.Q6

In this case I want to extract PMC00263ZP

Please consider that the number of characters before the first "/" can differ and therefor I cannot use the LEFT formula

You gave your own examples, but you ignored mine, so I still don't know whether my examples reveal any special needs. Let me ask again, but more wordily:

a) You say in the case of 'N' you "don't want to have anything". But (to be ridiculously technical about it) there's no such thing as not having anything; your program can take a number of different actions. It can skip that line and not react to it (which I guess can be thought of as not having anything, after all). Some people think of the space character as "not anything"—not most people, but it does happen. The most obvious candidate for "not anything" is the null string, ""; but more knowledgeable programmers would insist that the correct value is Null, which is something different. In Excel I think the Empty value is another one again. So my first question is this: When there's an 'N' in the second position (ie between the first and second slash), do you want your program to come up with "", or what?

b) I didn't ask this before, but what about 'n' (lower case); should it be treated as data, or the same as 'N'?

c) I assume that there's no situation in which you want 'N' to be the data itself?

d) Is there ever a case where the string is "-DR//FRU/2574/C.Q6"? Because if there is, I sort of assume your logic should treat it identically to "-DR/N/FRU/2574/C.Q6".

e) What about when the string is "-DR/"? Again, should it be treated the same as "-DR/N"?

If your answer is the same in all cases, then I think the Split function (as I described in my last entry) should work for you.

Hello Bob,

A. In my example -CL/N is taken from an email. So if /N or /n is encountered then excel can simply skip it

B. Both lower and upper case must be treated the same way

C. N is never going to be the data itself because it represents an empty space

D. No, there is never such a case

E. "-DR/" cannot exist. It must either be followed by "N" or the other string. That is mandatory.

If you think that Split is my best bet, can you please write me an example of it? how do I write it in, lets say, cell B1 if the data I am looking for in in A1? Further to that, the strings where I need to extract the data, are in cell A1:A45, and I need the results to be in B1:B45

Thanks for your patience

You might reconsider the LEFT function (for a text string located in A1, change as needed):
=IFERROR(LEFT(SUBSTITUTE(A1;LEFT(A1;FIND("/";A1));"");FIND("/";SUBSTITUTE(A1;LEFT(A1;FIND("/";A1));""))-1);"")
It will return nothing if there is no "/" in the text string, or if there is only one "/". Else, it will return the text between the first two slashes...
To eliminate duplicates, just copy/paste as values the results of the formulas, and use Data-remove duplicates.

Thank you Catalin and thank you Bob for your help.
This is what I was looking for

Yeah, I was talking about the Split function (in VBA) because you specified a macro in the original question. But if you can do it in the worksheet, without writing a macro, I'd use FIND and MID strings, not Split.

I haven't tested Catalin.B's solution, but I did notice it uses LEFT rather than MID. Just for variety's sake, here's my own idea using Excel worksheet functions: I'd do it not in just one column but several, each one simpler, with the helping columns out of the way out at the right. (Some people even hide their helping columns.)

In helping column X: =FIND("/",A1)
In helping column Y: =FIND("/",A1&"/",X1+1)
In helping column Z: =MID(A1,X1+1,Y1-X1-1)
In results column B: =IF(UPPER(Z1)="N","",Z1)

That uses more columns but each piece of it is easier to understand.