EXCEL: Easy way to extract characters from a String?

wageslave101

Board Regular
Joined
Jul 18, 2007
Messages
154
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

wageslave101

Board Regular
Joined
Jul 18, 2007
Messages
154
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,514
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jul 18, 2007
Messages
154
Thank you very much for all the response :biggrin:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,477
Messages
5,601,893
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top