Splitting text and extracting an element

[aa2000]

Hi all

I have a worksheet in which certain cells have data in the following format:

GROUP-ABCD-1.txt

Is it possible to separate text like the above with "-" as the delimiter and then set a string as the text between the 2 "-".

So if s is the string, then for the above example s would be ABCD.

Can this be done?

Cheers

 

[aa2000]

Well I have yet to get the split command to work, but that code works perfectly Peter, so I'll use that instead.

Thank you both very much for your help!

Cheers

 

[Peter_SSs]

Well I have yet to get the split command to work, but that code works perfectly Peter, so I'll use that instead.

Thank you both very much for your help!

Cheers

I'm glad you have something that is working for you.

However, I was hoping you would respond to my earlier question ..

To consider alternative approaches, could we have some more sample data and expected results so that we can see how much consistency/variation there is in the data to be dealt with?

.. since the response may possibly have led to a simpler solution. For example, consider these possibilities:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ExtractText()<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#007F00">'If always GROUP- then 4 characters then -</SPAN><br>    s = "GROUP-ABCD-10-1.txt"<br>    a = Mid(s, 7, 4)<br>    MsgBox a<br>    <br>    <SPAN style="color:#007F00">'If always GROUP- followed by ? characters then -</SPAN><br>    s = "GROUP-ABCDEFG-10-1.txt"<br>    a = Mid(s, 7, InStr(7, s, "-") - 7)<br>    MsgBox a<br>    <br>    <SPAN style="color:#007F00">'If ? characters then - then 4 characters then -</SPAN><br>    s = "GRP-ABCD-10-1.txt"<br>    a = Mid(s, InStr(1, s, "-") + 1, 4)<br>    MsgBox a<br>    <br>    <SPAN style="color:#007F00">'If ? characters then - then ? characters</SPAN><br>    s = "GR-ABCDEF-10-1.txt"<br>    a = Trim(Mid(Replace(s, "-", Space(100)), 100, 100))<br>    MsgBox a<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[aa2000]

Apologies Peter I seem to have missed your earlier post. The second example is the one that matches the actual data the most since.
The data in the cell is always prefaced with the same Word "GROUP" followed by "-" then the name, which varies in character length, then the numbers.

Once at work I will check the data and see if the second suggestion in your post above can also be used.

Thanks