EXCEL: Easy way to extract characters from a String?

[wageslave101]

Morning Guru's :D

I have a cell with a string, there are three letters that can appear at several places within the string.

For example;

280807GFX1900
GFX280807800
2808GFX2007T

I'm trying to write a check that returns TRUE/FALSE for the string containing GFX anywhere in it.

I've been working with MID, but that involves multiple checks of elimination, which led me to wonder if there's a way to check a string for the characters?

Thank you for your time,
WageSlave101

 

[wageslave101]

I found that I can use

=IF(ISERROR(FIND("GFX", B8, 1)), 0, 1)

Which returns a 0 if GFX does not exist anywhere in the string or 1 if it does appear anywhere in the string.

Does the job, but is the best way to do this?

WageSlave101

 

[pgc01]

Hi wageslave101

I'm trying to write a check that returns TRUE/FALSE for the string containing GFX anywhere in it.

In this case you don't need the if:

=ISNUMBER(FIND("GFX",B8))

Hope this helps
PGC

P. S. Find() is case sensitive. If you don't want that use Search() instead.

 

[Macropod]

hi WageSlave101,

If you wanted to find where, if at all, those characters are found, you could use:
=IF(ISERROR(FIND("GFX", B8)), 0, FIND("GFX", B8))
In this case, any non-zero number indicates the position of the first character in the string.

Cheers

 

[Scott Huish]

and if you want to return a 1 or 0 instead of TRUE/FALSE, as indicated in your formula:

=ISNUMBER(FIND("GFX",B8))+0

 

[wageslave101]

Thank you very much for all the response

I love this board and all the wonderful folks that contribute, I hope that all that karma rewards you all... you all deserve it!

Thank you again,
Wageslave