I'm programming a bunch of IF statements into a spreadsheet.

The statements take the text of one cell and post a formula result in
another cell, such as: IF B2 = "SP-129", B3="Supply"

I was wondering if I can have the formula just key off of the first two text
charachters in the cell, shortening it to something like: IF B2=SP..,
B3="Supply"

=IF(LEFT(B2,2)="SP","Supply","")
--
-SA


"Phil G" wrote:

I'm programming a bunch of IF statements into a spreadsheet.

The statements take the text of one cell and post a formula result in
another cell, such as: IF B2 = "SP-129", B3="Supply"

I was wondering if I can have the formula just key off of the first two text
charachters in the cell, shortening it to something like: IF B2=SP..,
B3="Supply"

You can use a LEFT statement, so that IF(LEFT(B2,2)="SP","Supply","Other")
An easier way may be to create a table of codes and then use a VLOOKUP to
match against the name, that way you don't have to write a bunch of nested
IFs. It would look like this:

=VLOOKUP(LEFT(B2,2), A:B,2,FALSE) where A and B is where you table of codes
reside. This would also allow you to dynamically update or add to your table
of codes as well as support more than 8 categories (based on a max of 7
nested IFs)
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Phil G" wrote:

I'm programming a bunch of IF statements into a spreadsheet.

The statements take the text of one cell and post a formula result in
another cell, such as: IF B2 = "SP-129", B3="Supply"

I was wondering if I can have the formula just key off of the first two text
charachters in the cell, shortening it to something like: IF B2=SP..,
B3="Supply"

Thanks, it worked. I was going crazy trying to figure this one out.

What my problem was is that I had a whole bunch of text entries I needed
converted over, but it came out to too many values to keep nesting IF
statements or LOOKUP values into one formula. But I noticed I could just key
off of the first two charachters and save a bunch of IFs. So now I know.




"Phil G" wrote:

I'm programming a bunch of IF statements into a spreadsheet.

The statements take the text of one cell and post a formula result in
another cell, such as: IF B2 = "SP-129", B3="Supply"

I was wondering if I can have the formula just key off of the first two text
charachters in the cell, shortening it to something like: IF B2=SP..,
B3="Supply"