Splitting text and extracting an element

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Sep 22, 2011

• #2

Try

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

 

Upvote 0

A

aa2000

Board Regular

Joined

Aug 3, 2011

Messages

87

• Sep 22, 2011

• #3

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

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Sep 22, 2011

• #4

Try like this

Code:

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

 

Upvote 0

A

aa2000

Board Regular

Joined

Aug 3, 2011

Messages

87

• Sep 22, 2011

• #5

Thanks Peter, this is what I was looking for.

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

Code:

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

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Sep 22, 2011

• #6

Try

Code:

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

 

Upvote 0

A

aa2000

Board Regular

Joined

Aug 3, 2011

Messages

87

• Sep 22, 2011

• #7

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

Cheers mate

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

65,116

Office Version

1. 365

Platform

1. Windows

• Sep 22, 2011

• #8

Another way to consider:

Code:

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

Code:

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

 

Upvote 0

A

aa2000

Board Regular

Joined

Aug 3, 2011

Messages

87

• Sep 26, 2011

• #9

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

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

65,116

Office Version

1. 365

Platform

1. Windows

• Sep 26, 2011

• #10

aa2000 said:

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

Click to expand...

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

 

Upvote 0