Formula To Identify Text and Numbers from a column

[SpAnKy]

Hi everyone.

Thanks to the help i got from this forum a while ago I have a nice little formula that is a great help for my work. Thanks. But now I need to expand it a little and make it analyze another column.

Basically I have

|---A---|---B---|
|1234-|---------|
|F110-|---------|
|--------|---------|
|F111|----------|
|1243-|---------|

So i want column B to look at column A and return if it contains a number = 2, If it contains the first character as an 'F' = 1, and if it is blank to return nothing. So would look like

|---A---|---B---|
|1234-|---2----|
|F110-|---1-----|
|--------|---------|
|F111|----1-----|
|1243-|---2-----|

Hope that makes sense.

[JBeaucaire]

In B1 then copied down:

=IF(ISNUMBER(A1), 2, IF(LEFT(A1, 1)="F", 1, ""))

[SpAnKy]

Is that all????

Man u guys are good, and quick.

[SpAnKy]

Thanks that worked a treat.

Cheers JB u rock

[SpAnKy]

Ok ive run into 1 little issue. Some of my numbers in COlumn 'A' are actually numbers stored as text and dont return correctly. Is it possible to have the formula Return the '2' if the cell doesnt contain an F??

[JBeaucaire]

Try this:

=IF(ISNUMBER(A1+0), 2, IF(LEFT(A1, 1)="F", 1, ""))

[SpAnKy]

Ok works perfect. So what does that 0 actually mean?

[JBeaucaire]

It tricks text-based numbers BACK into a number form, causing the ISNUMBER() test to result in TRUE.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.