Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a column of numbers. Some begin with the digit 8 and the rest begin with other digits. I want to write a formula that will return "2" if the number in column A starts with 8, and "1" if the number in column A starts with a number other than 8.

Is this possible and if so, how please?

share|improve this question

Using a combination of the Left and If functions...

=IF(LEFT(A1,1) = "8", 2, 1)

That's assuming you want to number returned as a number... if you want it as a string..

=IF(LEFT(A1,1) = "8", "2", "1")
share|improve this answer
This is fantastic! I've been searching all day for this. Can you please explain what the "1" after the A1 does? – Prebanks Mar 24 '11 at 18:25
@Prebanks it's the number of characters you want returned starting with the left-most character in the cell. The function syntax is LEFT(text, number of characters). – Ian C. Mar 24 '11 at 18:29
Ian, thank you so much for your help. You've saved the day! – Prebanks Mar 24 '11 at 18:34
And Brett you too ~ thank you for your quick response! – Prebanks Mar 24 '11 at 18:35

You must log in to answer this question.

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