How do I replace cell contents or add another column.

**KELLIS** · 05-07-2012, 05:31 PM

Hi Everyone

I have a spreadsheet where my table is imported from a csv. Column F shows a list of numbers 'Job Sub Type' which identifies to the type of job.
There are say 5 numbers from 17 to 22 (computer generated code). I would like to change the figures in this column or add a column that will show what the number means. The table is a list of jobs carried out on a date so there may be five entries of the 'JST' code number on the same date in the column.
i.e all 17's should be replaced with PP1, 18 should become PP2, 19 PP3, 20 MTR

Can someone tell me the way to go. I have been looking at IF REPLACE functions. I know what I want to do just not sure how to build the formula. Any suggestions would be great.

Thanks

Kellis

**martindwilson** · 05-07-2012, 06:46 PM

see next post

**martindwilson** · 05-07-2012, 06:47 PM

use a lookup or a vlook up. eg put 17,18,19,20,21,22 (er thats six numbers) say in k1,k2,k3.........k6 then in l1 l2 l3...l6 put their corresponding codes pp1,pp2......
then use lookup(f2,k1:l6)

**KELLIS** · 05-08-2012, 09:18 AM

Thanks Martin I tried the 'lookup' already and was really frustrated it wouldn't work, kept coming back n/a. When you suggested it I thought it must be something I was doing wrong it seems to be that column F imports as text so I need to change it manually to 'Number' format then it works. Do you know of anyway I can get it to import into a number format to allow my formula to work.
I am working on two ways to improve a spreadsheet, one the users will manually input the information which will also complete a summary page (at the moment they are two separate s/s) and two is to pull a daily report directly from the database as a CSV file import into my s/s so that the most my colleagues will need to do is audit the job codes to ensure they were closed down correctly. As I'm completely new to excel and teaching myself as I go I am not even sure this idea is viable or practical. So please forgive me when I ask probably Excel basic questions.

Thanks again for your help. I'm getting there and really enjoying the learning experience.

**GeneralDisarray** · 05-08-2012, 09:40 AM

you may have mis-authored something if you are getting N/a ... see the attached example (using VLOOKUP). basically, you need to reference a "key" which maps the number code to the appropriate letter code.

--EDIT--

this the formula used, my key range was in D2:E10...you would need to adjust that to fit your sheet. My code column was A, this is written in cell B2 and can then be copied/pasted down the column.

=VLOOKUP(A2,D$2:E$10,2,FALSE)