Extracting a code

[Fiona]

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

 

[Mike H]

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

 

[Bernard Liengme]

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 Liengme]

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

 

[Fiona]

Thank you Mike, thats exactly what I needed!

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

 

[Mike H]

Glad I could help

Thank you Mike, thats exactly what I needed!