EXCEL FORMULA WITH CARDS

  redelf 12:11 16 Jan 03
Locked
  redelf 12:11 16 Jan 03

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 03

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

So,

=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 03

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 03

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

in c1 type

=index(b1:b3,1,match("-NRH",a1:a3))

Is this what you were after?

  redelf 19:12 16 Jan 03

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:-

A B C D
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.

Thanks

  redelf 19:15 16 Jan 03

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 03

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 03

Cherria,

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…