Searching for a substring in a string after a specific number of specific characters

[rozias]

I am trying to extract a substring from a string that contains a varying number of characters. Example: BLAH BLAH TRN#1#0000000000#9999999999

I would like to identify just the 0's in the example string. I have been using the MID function (below), but the issues is with the number of 0's. It can vary in length.

=MID(A1,FIND("TRN#1#",A1)+6,10)

If there were only nine 0's, then my result would be 000000000#. Basically, I want to trim all characters from the last "#" and then run my MID function. Any help would be greatly appreciated.

 

[JLGWhiz]

see if this will work for you.

Sub t()
Dim spl As Variant, c As Range
For Each c In Range("A2:A1000") 'Range is arbitrary, use actual
    spl = Split(c.Value, "#")
        MsgBox spl(UBound(spl) - 1)
Next
End Sub

 

[FDibbins]

Try this instead...
=MID(A1,FIND("TRN#1#",A1)+6,LEN(A1)-LEN(SUBSTITUTE(A1,0,"")))

Note this will NOT work of there are 0's anywhere else in the string