0

I'm trying to write a formula that returns one of two values depending on the first letter in the string in cell E3.

I've tried various combinations so far, but keep getting it wrong so it only returns R. The string in cell E3 is an SKU made up of letters and numbers, but always starts with either F, G, Q or A, etc.

My formula:

=IF(OR(ISTEXT(LEFT(E3,1)*SEARCH("F*",E3)),ISTEXT(LEFT(E3,1)*SEARCH("G*",E3))),"L","R")

2
  • =IF(OR(LEFT(E3,1)="F",LEFT(E3,1)="G"),"L","R")?
    – Akina
    Jan 13, 2020 at 12:27
  • Thank you that works! I think I was over complicated it and ended up missing the point :)
    – Clare
    Jan 13, 2020 at 12:33

1 Answer 1

1

If there will never be an invalid value for the SKU, then:

=IF(OR(LEFT(E3,1)={"F","G"}),"L","R")

If you also have to ensure that the SKU starts with one of your letters, so as to exclude invalid values, then:

    =IF(OR(LEFT(E3,1)={"F","G"}),"L",IF(OR(LEFT(E3,1)={"Q","A"}),"R","invalid"))

Depending on your version of Excel, you may have to confirm this formula by holding down ctrl + shift while hitting enter

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .