redelf 12:11 16 Jan 2003
  redelf 12:11 16 Jan 2003

Trying to create an "if" formula in Excel which will include wildcard characters. Example:-

cell a1 contents are B-YRH 15
cell a2 BL-YRH
cell a3 CA-NRH

cells b1:b3 contain numerical values

I want cells c1:c3 to search by wildard for the characters before, or after, the hyphen in a1:a3 (eg *-YRH ) and show the numeric values from b1:b3 as appropriate. Whichever wildcard operater I use the result is always shown as "false" (but not as a formula error). Can anyone help please ?

  cherria 12:26 16 Jan 2003

=FIND() effectively uses wildcards, it returns the position of the text searched for in the text being searched.


=FIND("-YRH",a1) returns a 2
=FIND("-YRH",a2) returns a 3
=Find("CA-",a3) returns a 1

if it is not found you get a #VALUE error

  VoG™ 12:30 16 Jan 2003

You might also want to consider using =MATCH() which can look for wildcards. Then use =INDEX to return the number from column B.

Sorry, I have to dash! It's all in Help.

  cherria 12:32 16 Jan 2003

To find which one of the 3 has a match and return the value from row b try

in c1 type


Is this what you were after?

  redelf 19:12 16 Jan 2003

Thanks for the suggestions, but can't get them to do what I need. Apologies, I forgot to mention I wanted to replicate the formula down it's column. It would look like this:-

1 B-NRH 1.2 replicated as for
2 B-WRH 3.4 formula to Col. C but
3 C-NRH 3.5 show value when any
4 C-NRH 4.7 in Col. B entry in
5 B-YRH 1.2 if any entry Col. A
6 C-YRH 6.0 in Col. A includes
includes "-NRH". "-YRH"
(or if not, 0)

I want to be able to do this without having to keep changing elements of the formula. I can't get wildcards to work in the =IF function.


  redelf 19:15 16 Jan 2003

Sorry, ignore the middle para. in previous posting. I typed out to look vertical as a (poor) representation of the spreadsheet but it came up on the posting as gobbledook.

  cherria 10:55 17 Jan 2003

Sorry, I don't think I understand what you want but if you want to use wildcards in an IF such as

=IF(A1="*-NRH",B1,"Not matched") then you can do it like this:

=if(iserror(find("-NRH",A1)),"Not Matched",B1)

  redelf 01:12 21 Jan 2003


thanks for al the suggestions (sorry not go back sooner - been working away) - but can't get them do what I want Probably since I can't explain it clearly, so I will do it a more long-winded way just changing elements of the formula when I need to.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iPhone X review

How to find a font: Discover the name of a typeface with these apps

The best iPhone for 2017

Comment créer un compte PayPal pour payer en ligne ?