Converting multiple data codes to numbers

Hi there,

I have an extensive data set entered in excel.  The data includes several 
codes with number-letter combinations (e.g., 1a, 1b, 2f, etc).  I need to 
convert these codes to numerical values (e.g., 1a = 1).  There are too many 
to use the search / replace command.   Is there a formula I can use to change 
the codes more efficiently?

Thanks.
0
Utf
2/2/2010 2:49:01 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
835 Views

Similar Articles

[PageSpeed] 33

Assuming your codes are in one column then you may use VLOOKUP...

1. Set up two columns first with current codes (1a,...) and second with 
corresponding code you want (1,...), say in Col C and D
2. Now if you have your old codes in Col A then enter this in B1
=VLOOKUP(A1,C:D,2,False)
3. Copy the formula down

This will give you the new codes in Col B

4. You can copy Col B and PASTE SPECIAL->Values over Col A

"Natasha" wrote:

> Hi there,
> 
> I have an extensive data set entered in excel.  The data includes several 
> codes with number-letter combinations (e.g., 1a, 1b, 2f, etc).  I need to 
> convert these codes to numerical values (e.g., 1a = 1).  There are too many 
> to use the search / replace command.   Is there a formula I can use to change 
> the codes more efficiently?
> 
> Thanks.
0
Utf
2/2/2010 3:06:01 AM
Reply:

Similar Artilces:

Convert excel to java
Hi does anybody know a way to convert an XL spreadhseet (with charts) to a fully interactive Java object?? I have tried exceleverywhere and xlsius, but no luck! Thanks in advance -- shnim1 ...

trying to return data with only the latest date
when i run this i get data for the last day. i'm trying to get it to return only for the latest date. for example the instance name column contains all the drive letters. i would like it to return one row for each machinename and drive letter. tried adding distinct but didn't work with free_hd_space_cte as ( select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue, convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead from counterdetails a inner join counterdata b on a.counterid = b.counter...

Synchronise Calendar items across multiple domains
Can Canlendar items be synchronised across multiple domains in an MS Exchange forest? Thanks in advance ...

SumProduct over multiple sheets
Hello The below formula does one sheet. I have ten. Is there a way to have it sum all ten for me that does not require a long formula Thanks for your help =SUMPRODUCT(('Line7'!$C$15:$C$114=C7)*('Line7'!$G$15:$G$114='Numbers'!$S$9)*('Line7'!$AF$15:$AF$114)) I'd use 10 different formulas. Then sum them in an eleventh formula. In fact, if you put each of those formulas in a dedicated cell (say A1 of each individual sheet), you could use a formula like: =sum('line1:line10!a1) As long as all the other sheets are between line1 and line10. Jack wr...

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <FrankWinston@discussions.microsoft.com> wrote in message news:7CAF0A61-E8A7-4D18-B035-0D475E9A858C@microsoft.com... > Is there a way to convert a column of text, date values (entered as 81096, > for example...

Multiple Instance of Same code
I have a form that lists duplicate Batch Job Names (form is based on a table that has job name and job nbr as primary key). I want to give the user the ability to click on the batch job name and open a new form (Batch Job Steps) that will show the job steps for that batch job and if needed click on the duplicate job name and show the job steps. In other words: Batch Job A (12 Job Steps) & Batch Job A (16 Job Steps). Both of these records have job nbr that form part of the primary key but is hidden from the user.) I was trying to use the code below and filter the form on load...

Lebans Report to PDF for Multiple Reports
Hi: I'm using Mr. Leban's Report to PDF utility with great results, however, I need help taking it one step farther... I have several different reports open at once. Each contains different info, but they are all needed to satisfy a reporting requirement. Workflow had been to print each report to an individual PDF file, then get an admin person with a full version of Acrobat to combine the individual file into one file for document retention. I'd like to be able to cut out the admin person step and use the Leban's utility to print all open reports to a single file. Any su...

How do I change a data label on an xy scatter to a unique name as.
The only way I have figured out is to create a separate series for each data point, which is tedious for 100+ points...any ideas? Hi, Try one of these free addin to link cells to data labels. This way you can have a single data series rather than multiples. Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Cheers Andy SOSCIENT wrote: > The only way I have figured out is to create a separate series for each data > point, which is tedious for 100+ points...any ideas? -- Andy Pope, Microsoft MVP - Excel http://www.andyp...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

how to create button commande to refresh data in query in excel 2.
I have a excel spreedsheet that contain external data. I would like to put a button in the excel sheet to update the sheet without doing right click and refresh. My user here are very dummies. Jean Francois If this is external data then bringing it in should launch the external data toolbar. They will only need to click the exclamation (!) mark. Seems like re-inventing the wheel. If you must then put a button on the worksheet and assign it to this macro Sub refreshdata() Dim wks As Worksheet Dim qryTab As QueryTable Set wks = ActiveSheet For Each qryTab In wks.QueryTables qryTab.Refr...

MSP_EpmAssignmentByDay
We have quite a lot of projects in Project Server. Should there be data in MSP_EpmAssignmentByDay table on ProjectServer_Reporting db? Now the table is totally empty. How can we get data to that table? ...

Convert list of pairs [a(i),b(i)] to table that shows # of occurrences of each pair
Given something like this: apple,jan apple,feb apple,mar apple,jan apple,feb orange,jan orange,feb orange,jan orange,feb banana,jan banana,feb banana,mar banana,jan banana,feb I'd like to create a table that has three rows (the fruits) and three columns (the months). At the intersection of each row and column I'd like the number of occurrences of that particular fruit-month pair. Thanks for your time. Tom Z. Tom, Assume your data is in the range A1:A14, and you have a grid starting in cell C1 that looks like: C D E F 1 jan feb mar 2 apple 3 orange 4 banana ...

List a group of repeated data from sheet1 to sheet2 but not repeat
-- thadi I have one excel sheet1 look like the following. UserName Location Division Software Name Version Maria.Sigmund Parkade Probation Adobe Reader 7.0 Maria.Sigmund Parkade Probation Lotus Notes 6.5.5 Eric.Bell Parkade IT WebFldrs 1.0 Eric.Bell Parkade IT Adobe Reader 7.0 Eric.Bell Parkade IT Symantec AntiVirus 8.1 I want to modify this sheet1 to sheet 2 ...

Frogs in the pond
Hi, I have this little charting problem. I'm sure someone will have the solution. It's difficcult to explain so I 've posted a picture here www.fransysco.net/frogs/frogs.gif You may have to zoom on your browser to see it perfectly. I have some data in a table and it's figures about frogs in a a pond and about how many arrive in each batch per quarter and how long they survive. The length of survival is measured in Quarters, too. So each quarter there's a batch of new frogs that arrive - usually 2000 in a a batch. And I measure the survival rate of each batch. Now, if I al...

imported customer data not showing up
After import was done I went into Rms but could not find the customers. What am i doing wrong. This is becoming a nightmare. Did you have errors when importing? Did you include a unique Account Number for each Customer? Did any customers show up? Rob "country" <country@discussions.microsoft.com> wrote in message news:74E7CD37-AD87-4C36-A5AE-6966D4147838@microsoft.com... > After import was done I went into Rms but could not find the customers. > What > am i doing wrong. This is becoming a nightmare. ...

Number formats and fields
Hi All! I have a list of equations in a word document that I need to add a reference (the first equation in the document will be equation A, the second will be equation B, etc). So I need a numbering field. However, when I get to equation 28 I want the format to be AB, then 29 to be AC, not BB, CC like the default A, B, C… numbering gives you. However, I will be adding other equations randomly throughout the document and referring to the equation reference in text paragraphs, so I would like them to auto update when I add another equation in the middle of the document. Doe...

auto refreshing data in pivot table
hello friends, i am using ms access 2003, vb6. i want a pivot table get refreshed automatically when i open the form. but it is showing me the previous results. so i explicitly needed to refresh manually. how can we refresh data in a pivot table programatically in vb. thanq ...

Find data in two ranges
Hello, I have a worksheet with in the same row two different ranges: one with nummeric data one with time values. These two ranges works as follow the first nummeric data cooresponds with the first time value, the second nummeric data corresponds with the second time value. For the nummerric data I calculated the best of 3. Now I need the corresponding time value for those best of 3. Thanks for your help with this one. Someone replied to your other post and asked for more detail. -- Biff Microsoft Excel MVP "Santafe" <Santafe@discussions.microsoft.com> w...

Item Tax Change
Last night I changed all of our Coupons Item Tax from a Cou-Tax to MO. That switched them from Cou-Tax to MO taxable. so that when a coupon is used it will deduct the sales tax (Missouri Tax Law) and I assigned the Sales Tax "Non-tax" to the Item tax "Non-tax" for our non tax items like labor. (this was never set up from 2 years ago, I don;t know if this was need, or if it is causing the problem) Now every transaction on every item on POS requires a reason code "for tax change". Even though they are not using a coupon or a non-tax item only items with MO I...

How to increment page number in a cell of worksheet
I have a problem regarding page numbers in a worksheet. The position of page number in the cell is such that page number cannot be provided through header or footer. Can you guide me about how can I increment page numbers in a cell. This is a very unreliable workaround. Declare col. "A" to be an Helper-Column. In Cell A1 type the following formula and copy down to the end of your data. Col. A Cells should, now, present the page number. Pull out one of each, in a "page", to another column in order to present the page number. You may fine-tune, the formula, b...

Chart Help
I have to chart the following I have multiple users with 4-5 different status and multiple dates, some sample data is below 03/02 - name 1 - open - 5 items 03/15 - name 1 - open - 10 items 03/02 - name 2 - open - 0 items 03/15 - name 2 - open - 10 items 03/15 - name 2 - closed - 5 items 03/02 - name 3 - open - 5 items 03/02 - name 3 - pending - 8 items 03/15 - name 3 - open - 14 items I need to show a trend by user by status & by date I'm not sure how the table needs to be set up or if the chart can be done for this. Could someone please provide assistance? Thanks In articl...

convert pub file to wpd file?
I use Publisher for all my needs, but a lot of my contacts do not. Is it possible to convert Publisher to word perfect document or any other format that woud be easier for them to open. ...

Convert file from Money 2003
I upgraded to Money 2007 Premium. When I 1st launch and sign in it tells me it is going to convert my money file to 2007. I see the "converting file" status bar come up but then it doesn't ever seem to convert the file. Any suggestions? Hey; I am having the same problem with the trial version of Money Deluxe 2007. The "converting file" screen appears, but the program stalls. I am attempting to move from Money 2003 to Money 2007. Did you ever get this problem solved? If so, how? Thanks -- Go Twins "allensxs3" wrote: > I upgraded to Money ...

Multiple jobs are waiting in side....
Hi Job seekers... Find the job that's right for you. Search for Job Vacancies across Top Companies in India. this is the right one to find all jobs here http://a2zinformations.blogspot.com ...

parse data from text field
I currently have a field in a form that contains the exact path to files stored on my computer (ex. C:\file1\file2\file3\samplefile.doc). The path to the file is different for most of the files. I have changed some things in the database and I would like to parse just the file name and extension (ex. samplefile.doc). I have tried the Mid, Left, Right, and InStr functions but I have not been able to get the desired data. Any help with code to parse the data correctly would be appreciated. The names of files are not equal in length. Thank you accessuser1308, You kinda ...