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

 

[VoG]

Try

=MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",SUBSTITUTE(A1,"-","~",1))-1)

 

[aa2000]

Whilst that does work, this code is supposed to be part of a larger macro, so can this be done using VB?

Sorry for the confusion, and thanks for your help

 

[VoG]

Try like this

Sub test()
Const s As String = "GROUP-ABCD-1.txt"
Dim a As String
a = Mid(s, InStr(s, "-") + 1, Len(s) - InStrRev(s, "-") - 1)
MsgBox a
End Sub

 

[aa2000]

Thanks Peter, this is what I was looking for.

I have modified the code as below, but MsgBox is blank. Any ideas why?

Sub test()
Dim s As String
Dim a As String
s = Cells(1, 1).Value
a = Mid(s, InStr(s, "-") + 1, Len(s) - InStrRev(s, "-") - 1)
MsgBox a
End Sub

The value in Cells(1, 1) is GROUP-ABCD-1 since the .txt part does not need to be there.

Cheers

 

[VoG]

Try

Sub test()
Dim s As String
Dim a As String
s = Cells(1, 1).Value
a = Mid(s, InStr(s, "-") + 1, InStrRev(s, "-") - InStr(s, "-") - 1)
MsgBox a
End Sub

 

[aa2000]

Thanks a lot Peter, it does exactly what I required!

Cheers mate

 

[Peter_SSs]

Another way to consider:

Sub ExtractText()
    Dim s As String, a As String
    
    s = "GROUP-ABCD-1.txt"
    a = Split(s, "-")(1)
    MsgBox a
End Sub

If there could be extra unwanted spaces in there then

a = Trim(Split(s, "-")(1))

 

[aa2000]

Thank you both for help so far.

Now I am using VOG's code currently for cells with data in the form "Group-ABCD-1, but how can I modify the code to work for data in the form "Group-ABCD-10-1" ?

Cheers

 

[Peter_SSs]

Thank you both for help so far.

Now I am using VOG's code currently for cells with data in the form "Group-ABCD-1, but how can I modify the code to work for data in the form "Group-ABCD-10-1" ?

Cheers

Try my code? (Assuming you still want "ABCD" returned from that string)