IF when there is text involved..

[iveetsa]

I understand how to use the IF function for numbers (one of the very few things I do understand..!).

I have a worksheet that has a list of codes in column C. These codes take the form 1F, 2F, 3F, 1P, 2P etc and the number relates to a year. In column I, I would like to have a list of the years.

Is it possible to use the IF function when there's text involved, or am I going to have to delve into the dreaded macros? Can I get it to read from column C and put in column I ie. "If C3 says 1F, I3 should say Year 1. If C5 says 2F, I5 should say Year 2".

Any help would be greatly appreciated..

[zbor]

You could better use VLOOKUP function in this case.
make a table, define values and then with VLOOKUP formula assign them to the text.

[iveetsa]

Wow, thank you for the quick response! I've had a quick google (!) and think I understand how that would work, thank you. The codes come from an external data source so the value in the C column is actually a formula.. would the VLOOKUP be able to recognise that? I'm unsure of it's capabilities - I will have a play and see what I can do!

[zbor]

Or upload example workbook and we'll fix it for you

[iveetsa]

Here's an exceptionally minimal version of the workbook - sorry, I had to delete about 99% of it but the relevant things for this question are there. The data is drawn from the Tardis 27 Jan worksheet (which is generated by a website, then imported as a text file). Column I ("Year") is where all the Year 1, Year 2 blah blah should appear.

(I don't know if this effects it or not, but the names in column I will be used to name subdirectories later in the process - currently the it creates the file based on the code, but I would like to rename them to create the folders named as Year 1, Year 2 rather than 2F, 1P etc.)

Thank you so much!

[zbor]

Of course, you are supposed to remove all private data.

Look at the example:

[iveetsa]

Thank you very much - I have tried to alter the formula so that the session code (1F etc) still shows in column C and the years (Year 1, Year 2 etc) show in column I ("Years") but it's not having any of it - just the formula is showing up so I'm clearly doing something very wrong..

Also, just double-checking, it is set up so that it would pick up codes such as "3F9HH" as being "Year 3", yes? Obviously I could assign all individual codes manually, but NumberLetter is always the root of the code, so I'm hoping it will look at things that just start with '3F' or '2P' regardless of what come after them, and still assign them to the correct year...

[zbor]

Is number always first digit in the left?

[iveetsa]

Yes, the first digit of the code will always be a number (usually 1, 2 or 3 but can be up to 9!) and the second always either F or P. There may or may not be anything after those two digits. But always a number first!

[zbor]

So something like: ="Year "&LEFT(formula,1) (where formula is your current formula in the cell)