Hi, I have a list of titles that all contain a 6 digit reference ABC123, the
first 3 digits are always the same (ABC) but the second 3 change, they are in
a different places on each line, so the MID function doesn't work.

Is there anyway to =ABC and the 3 digits after it??


thank you

Fiona

If I understand correctly you have strings like

qwertyABC999jjnsccjkn

and you want the 999. If so try this

=MID(A1,SEARCH("abc",A1)+3,3)

Mike

"Fiona" wrote:

Hi, I have a list of titles that all contain a 6 digit reference ABC123, the
first 3 digits are always the same (ABC) but the second 3 change, they are in
a different places on each line, so the MID function doesn't work.

Is there anyway to =ABC and the 3 digits after it??


thank you

Thank you Mike, thats exactly what I needed!

"Mike H" wrote:

Fiona

If I understand correctly you have strings like

qwertyABC999jjnsccjkn

and you want the 999. If so try this

=MID(A1,SEARCH("abc",A1)+3,3)

Mike

"Fiona" wrote:

Hi, I have a list of titles that all contain a 6 digit reference ABC123, the
first 3 digits are always the same (ABC) but the second 3 change, they are in
a different places on each line, so the MID function doesn't work.

Is there anyway to =ABC and the 3 digits after it??


thank you

Glad I could help

"Fiona" wrote:

Thank you Mike, thats exactly what I needed!

"Mike H" wrote:

Fiona

If I understand correctly you have strings like

qwertyABC999jjnsccjkn

and you want the 999. If so try this

=MID(A1,SEARCH("abc",A1)+3,3)

Mike

"Fiona" wrote:

Hi, I have a list of titles that all contain a 6 digit reference ABC123, the
first 3 digits are always the same (ABC) but the second 3 change, they are in
a different places on each line, so the MID function doesn't work.

Is there anyway to =ABC and the 3 digits after it??


thank you

With the first entry in F1, try these and see if any do what you want
=RIGHT(F1,LEN(F1)-3)
=TRIM(RIGHT(F1,LEN(F1)-3))
=SUBSTITUTE(F1,"ABC","")
=TRIM(SUBSTITUTE(F1,"ABC",""))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Fiona" wrote in message
...
Hi, I have a list of titles that all contain a 6 digit reference ABC123,
the
first 3 digits are always the same (ABC) but the second 3 change, they are
in
a different places on each line, so the MID function doesn't work.

Is there anyway to =ABC and the 3 digits after it??


thank you

I meant to add: to get numbers not text use the double negation or VALUE
=--RIGHT(F2,LEN(F2)-3)
=VALUE(TRIM(RIGHT(F3,LEN(F3)-3)))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Fiona" wrote in message
...
Hi, I have a list of titles that all contain a 6 digit reference ABC123,
the
first 3 digits are always the same (ABC) but the second 3 change, they are
in
a different places on each line, so the MID function doesn't work.

Is there anyway to =ABC and the 3 digits after it??


thank you