Conditional Formula Help ? (IF, OR, LEN(), etc.)

hello, first post here.

I'm trying to write a function to format a column of cells depending on a set of conditions.

The attached spreadsheet will give better examples.

If Cell A1 is 9 alpha numeric and starts with "S999" then display "ONE", if A1 is 9 alpha numeric but does not start with "S999" then display "TWO"

OR

If Cell A1 has less than 9 alpha numeric display "THREE"

Thx!

EDIT: I have 2003 AND 2007... thx!

Put this formula in B3 and copy down:

=IF(LEN(A3)=9, IF(LEFT(A3)="S999", "ONE", "TWO"), "THREE")

edit: Ooops, I think I misread the question. Did not factor in the length of the string, but instead went for distinguish between pure alpha and alphanumeric. Ah well, maybe someone else will need that, so I'll leave it here.

Hi, welcome to the board.

It's ugly but it can be done. Generally, Excel does not distinguish between text and alphanumeric. If it contains letters, then to Excel it's text. So we need to check if the text contains the digits 0-9. The formula below uses the SEARCH function for that. Unfortunately, SEARCH (and FIND as well) return #VALUE when the search item is not found, so we need to embed it into an IF statement to convert the #VALUE to a zero and a successful find into a 1.

We add up the results for all the searches for digits 0 - 9. If the sum is 0, it means that the cell is purely alpha, so the formula returns THREE. If the som of the search is a number greater than zero, it means that at least one digit has been found, so the text is alphanumeric. So now we check the four leftmost characters and if they equal S999, return ONE, otherwise, return TWO.

Here is the beast:

Please Login or Register  to view this content.

put into cell B3 and copy down.

enjoy

It's not displaying "ONE" and I think I meet all the conditions in the attached sheet.

Any thoughts?

EDIT: this is where I was/am stuck:

=IF(LEN(A3)=9, IF(LEFT(A3)="S999", "ONE", "TWO"), "THREE") this will only display "TWO" and "THREE" but not "ONE"

which leads me to believe that because the cells that contain "S999" are all also 9-digit alpha numerics it defaults to "TWO"? (or I don't fully understand IF,OR,LEN() functions...

Thx for any input or thoughts. Very much appreciated.

I thought the LEFT() function in the formula looked a bit odd. Try this

=IF(LEN(A3)=9, IF(LEFT(A3,4)="S999", "ONE", "TWO"), "THREE")

(*ashamed*) Sorry about that. Noob mistake on my part.