FIND Option

[JTL9161]

I have several strings of data each 200-500 characters. I am trying to collect the 9 places of data within the string that come after a constant "EX". For example: 12345DEFCT444556EX987654321XX2DS84EGSS5....

There will be only 1 EX in the line each line so in this example I am trying to collect the 987654321.

I have tried various formula's with FIND but I cannot seem to dissect out the 9 character string.

I would appreciate and help.

Thanks,
James

 

[Fluff]

How about
=MID(A1,SEARCH("ex",A1)+2,9)

 

[ganeshpoojary05]

Assuming your data is in A2 column. Use the below formula.

=MID(A2,FIND("EX",A2)+2,9)

 

[JTL9161]

Thanks to both of you!

 

[JTL9161]

Since I have you here. I have another formula =MID(M13,FIND(",",M13)+1,256) but sometimes after the "," there can be a dash "-").

Is there an IFERROR formula I can tie in to default the "-" to be something like 5.00? I am adding 2 columns and when the "-" appears the sum of the 2 is off.

Thanks

 

[Fluff]

Maybe something like
=SUBSTITUTE(MID(M13,FIND(",",M13)+1,256),"-","5.00")

 

[JTL9161]

Perfect! Thanks

 

[Fluff]

You're welcome & thanks for the feedback