Picking out a single data from a string of data

[shantigal]

Hi,

I am trying to pick out the ERAP no.s from a string of data. Which part of the data string it appears is inconsistent. So, i cant use the text to columns function. In my excel sheet ,there is a set of data like i mention afore. Is there a way to still pick out just the ERAP no.s to copy and paste them onto the column next to the data string?

Nt: some data string do not have the ERAP no.s - i wish to leave the field blank in that case...

someone please enlighten me...


your help will be much appreciated ...

 

[qafself]

Hi shantigal

Assuming the numeric part is always 5 digits

Put this in the required cell and copy down

=IF(ISERROR(MID(A1,FIND("ERAP",A1),9)),"",(MID(A1,FIND("ERAP",A1),9)))

If you just want the number,

=IF(ISERROR(MID(A1,FIND("ERAP",A1),9)),"",(MID(A1,FIND("ERAP",A1)+4,5)))

let me know how you go

Ed

 

[shantigal]

It works perfectly well... I had to removed the auto filter before that though...

Thank you !

 

[qafself]

Glad to hear it worked for you.

Thanks for the feedback

Ed