How to extact word from text?

[djamy]

Can some one tell me which formula allows me extracting "9271" from this text :

FRANCE TELEVISION - MARSEILLE - 78 - 92- 95 - 9271 - OUEST MARESEILLE LIEC - TEST IMMO - 04/09/2009 - 03/09/2012

No VBA

Thanks a lot in advance

 

[barry houdini]

Hello djamy, welcome to MrExcel

The answer depends on how consistent your data is. For example if your data is in A1 then this formula works for that example

=MID(A1,47,4)

That just takes the 4 characters beginning at character 47. I assume it won't be as easy as that though. Which parts are consistent within your data? Perhaps show 5-10 other examples......

 

[djamy]

Hi Barry !

Thanks for your reply

In fact, iI wanna know if I can exrtact "9271" from these 2 texts with the same formula :

FRANCE TELEVISION - MARSEILLE - 78 - 92- 95 - 9271 - OUEST MARESEILLE LIEC - TEST IMMO - 04/09/2009 - 03/09/2012

and

FRANCE TELEVISION - BRETAGNE - 9271 - BRETAGNE LIEC - TEST IMMO - 04/09/2009 - 03/09/2012


Beacause I'll applay it for a huge set of data


Thanks a lot in advance

Djamy

 

[barry houdini]

That still leaves several possibilities....

Assuming that you always have two dates in that format at the end then this formula will ignore those and extract the last 4 digit value

=LOOKUP(10000,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-27)),4)+0)

 

[djamy]

Ah great !! It works very well !!
Many thanks Barry