Extracting a code

F

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
 
M

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
 
B

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
 
B

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
 
F

Fiona

Thank you Mike, thats exactly what I needed!

Mike H said:
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
Click to expand...
 
Top