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]

I tried your code as well Peter, but I get an error message saying:
"Compile Error: Wrong number of arguments or invalid property assignment"
and the word "split" is highlighted.

Any ideas why?

Cheers

 

[Peter_SSs]

I tried your code as well Peter, but I get an error message saying:
"Compile Error: Wrong number of arguments or invalid property assignment"
and the word "split" is highlighted.

Any ideas why?

Cheers

1. What version of Exel are you using?

2. In the VB editor, check in Tools|References for any missing references

 

[aa2000]

Im using excel 2003. I am not sure what references should be there by default so I am not sure if any are missing. However I have not edited the available references, so is it likely that any are missing?

Cheers

 

[Peter_SSs]

Any missing required references would show in the list as missing. Refer: http://www.cpearson.com/excel/missingreferences.aspx

Did you Copy/Paste my code, or re-type it yourself?

 

[Peter_SSs]

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?

 

[aa2000]

Well using the method from that link, no references are missing and the code was copied directly from your post.

 

[Peter_SSs]

Does this still error?

<font face=Courier New><br><br><br><SPAN style="color:#00007F">Sub</SPAN> ExtractText2()<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>    s = "GROUP-ABCD-10-1.txt"<br>    a = Split(s, "-", 3, 1)(1)<br>    MsgBox a<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Have you tried closing right out of Excel and re-opening?

 

[aa2000]

Unfortunately this does as well with the split command higlighted. I will try this on a different machine running excel 2010, as well as checking the references again and get back to you about this problem.

Cheers

 

[VoG]

In response to your PM, Peter's code works fine for me, as expected.

 

[Peter_SSs]

If, for some reson, the Split cannot be made to work on your machine, perhaps this might suffice.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ExtractText3()<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>    s = "Group-ABCD-10-1.txt"<br>    a = Trim(Mid(Replace(s, "-", Space(100)), 100, 100))<br>    MsgBox a<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>