Find and extract text string from within another text string

Hi guys,

I have a text string that has a format like this ".....&id=xxxxxxxx&....... I want to find and grand "id=xxxxxx". Is there a formula or macro that could help me do this?

Thanks.

I don't know a better way but you can try this. Assuming the text string is in A1 :

=MID(A1,FIND("&",A1,1)+1,(FIND("&",A1,FIND("&",A1,1)+1)-(FIND("&",A1,1)+1)))

Hi millz,

Thanks for your quick reply. I am sorry I did not put it right. There is a bunch of other "&"'s in the string. I think your formula would work if there was no other &.

Then maybe this:

=MID(A1,FIND("id=",A1,1),(FIND("&",A1,FIND("id=",A1,1)+1)-(FIND("id=",A1,1))))

That works! Thanks a lot millz!

Hi millz,

I know you helped me solve the problem and thank you so much for that. I am now trying to understand the logic behind your code and can't seem to make sense out of it. Could you help me explain it? Appreciate it!

Thanks.

let's say the full string contains "ZZZZZ&id=1234567&ZZZZZ"

=MID(A1,FIND("id=",A1,1),(FIND("&",A1,FIND("id=",A1,1)+1)-(FIND("id=",A1,1))))

FIND("id=",A1,1) would return 7 because the first occurrence of "id=" starts at position 7 ZZZZZ&id=1234567&ZZZZZ

so it becomes:

=MID(A1,7,(FIND("&",A1,7+1)-(7)))

next,

=MID(A1,7,(FIND("&",A1,7+1)-(7)))

FIND("&",A1,7+1) is telling Excel to find for "&" starting at position 8 (which is after the first occurrence of "id="), hence returning the position of the first "&" after "id=". This would return 17 ZZZZZ&id=1234567&ZZZZZ

so the formula would end up with:

=MID(A1,7,(17-(7)))

which also means, returns the string starting from position 7, for the length of 10 characters (17 - 7)

ZZZZZ&id=1234567&ZZZZZ
1234567
      1234567890

Results: id=1234567

hope this explains

Hi millz,

Thank you for your detailed explanation. Work has been busy and I don't have time to actually look at it yet. Really appreciate it though.