Convert Alpha to Numeric

I have two spreadsheet which I would like to compare.  One has numeric data, 
the other has the same data but the references codes have been entered as 
alpha.  Does anyone know how this can be converted to numeric?

Many thanks for your help.
0
Vicki (34)
3/21/2005 5:45:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
420 Views

Similar Articles

[PageSpeed] 14

Not sure, but maybe as a start, you could try something along these lines
using VLOOKUP and CODE() ?

Assume the sample (numeric) data below is
in Sheet1 cols A and B,
with the numeric refcodes in col A

RefCode Field1
65 Text1
66 Text2
67 Text3
etc

And you have in Sheet2,
the alphas as the RefCodes in col A

RefCode Field1
A Text1
B Text2
C Text3
etc

Put 2 labels into C1:D1  : Compare, Result

Put in C2: =VLOOKUP(CODE(TRIM(A2)),Sheet1!A:B,2,0)
Put in D2: =TRIM(B2)=TRIM(C2)

Select C2:D2 and fill down

Col C ("Compare") extracts over the matched items for the RefCodes (A,B,C)
from the col Field1 in Sheet1 for comparison, while col D (Result") gives
the result of the comparison of col C with what you have in col B in Sheet2
(TRUE = yes, matched ; FALSE = no, does not match)

For the sample data above, what you'll get is:

RefCode Field1 Compare Result
A Text1 Text1 TRUE
B Text2 Text2 TRUE
C Text3 Text3 TRUE
etc

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Vicki" <Vicki@discussions.microsoft.com> wrote in message
news:E6ED460F-3D5F-4E25-A3D2-612759F521FE@microsoft.com...
> I have two spreadsheet which I would like to compare.  One has numeric
data,
> the other has the same data but the references codes have been entered as
> alpha.  Does anyone know how this can be converted to numeric?
>
> Many thanks for your help.


0
demechanik (4694)
3/21/2005 7:12:45 AM
On Sun, 20 Mar 2005 21:45:02 -0800, "Vicki" <Vicki@discussions.microsoft.com>
wrote:

>I have two spreadsheet which I would like to compare.  One has numeric data, 
>the other has the same data but the references codes have been entered as 
>alpha.  Does anyone know how this can be converted to numeric?
>
>Many thanks for your help.

If I understand you correctly:

Information that looks like a number but is really TEXT can be changed to a
number by performing some kind of arithmetic operation.

So, for a comparison, if the numeric data is in A1 and the TEXT data is in B1,
and both of them are 12345; the formula:

=A1=B1  	:FALSE
=A1=--B1	:TRUE

The double unary preceding B1 forces Excel to convert it to a number.


--ron
0
ronrosenfeld (3122)
3/21/2005 12:01:33 PM
Reply:

Similar Artilces:

can you convert an excel document to a powerpoint doc,
i have an organizational chart in excel and need to convert it to powerpoint. Is this possible. "dolphin" <dolphin@discussions.microsoft.com> wrote in message news:99472059-A5CE-4C59-B5CD-779DEA1A9D4F@microsoft.com... > i have an organizational chart in excel and need to convert it to > powerpoint. > Is this possible. Tried Insert-Object? ...

How do I get more than "Convert to PDF" in AutoText?
The AutoText dropdown menu provides a full list of options (e.g. Closing) but, the only selection available in each submenu is "Convert to Adobe PDF". How do I get the AutoText selections back? I'm not sure what has happened here... A poorly designed add-in perhaps? Try this: Exit Word and rename normal.dot (the blank document template). Restart Word. A fresh copy with factory default settings will be created. Note that in order to locate normal.dot, you can search for it in Windows; make sure that you search hidden files and folders. You can start the Find util...

File not Found after converting Access 2000 db to 2007
When I converted a particular Access 2000 db to the new .ACCDB format, I get 'File Not Found'. There is no number, just the message. I have tried opening the unconverted file in Access 2007 without any problems. It's only after converting it. I have tried to use Sysinternal's FileMon program to see if I can determine the missing file, but I haven't had any success. Any help would be appreciated. If there is a connected back-end, that may be the problem. It may however be code failing. Go into design view of the module and set a breakpoint to debug the problem. -- ...

How to convert hyperlink in Excel to show hyperlinked address?
I have an Excel spreadsheet with a list of hyperlinked email addresses. For example the list displays "Email", but the hyperlinked data is "mailto:name@domain.com". I need to "un-hyperlink" the information so that I have a list of the data itself - i.e. so I have a list of the name@domain.com addresses. Using Excel 2003. Thanks for any help you can provide! IN EXCEL 2007 (maybe 2003 but not sure) / right click the cell and select Remove Hyperlink. If my comment has helped please hit Yes. Thanks. "Bruce" wrote: > I have ...

Convert Function Result To Plain Text
I want to copy the result of a concatenate function to another cell as plain text (cell contents is the textual result not the function statement). I can't seem to find the function that does that. Can you steer me to it please. There is no such a function (think it over: it's impossible), it can be done with Copy/PasteSpecial-Values! -- Regards! Stefi „"Questor"” ezt írta: > I want to copy the result of a concatenate function to another cell as plain > text (cell contents is the textual result not the function statement). I > can'...

Converting Pocket Excel .pxl to Excel 2003?
I wish I could make the subject a little more detailed but it was long enough. I wanted to find out how I can convert a .pxl file which I beamed via the Infrared port on my iPaq to a PC which does not have ActiveSync or anything like that loaded on it? An example would be if I was at a customers site and I needed to retreive a file from my iPaq but the receiving laptop does not have anything to conver the .pxl file to Excel 2003. Normally I would have my own laptop with me and I would just use my ActiveSync to convert but there are plenty of times where this situation would occurr. ...

Excel file saves as a temp file with numeric file name
I have a bug with an excel worksheet. When i save the file it saves it not as my filename, but as a temp file with a name like D7d98300 and the file icon is of the MS DOS variety. When i click on the file it opens up in excel. However when i save it again it saves as a temp file with a different numerical file name. Could someone please explain what the bug is? Thanks in anticipation. Michael --- Message posted from http://www.ExcelForum.com/ When you save a file, excel will save it as a temporary file (8 characters, no extension). Then if that save is successful, excel will delete the...

How can I convert an exported UNIX timestamp in excel?
When I export a table using phpMyAdmin I get a date string of: 1064272434 Which equates to: 09/22/2003 07:13:54 PM Is there a formula for excel that would make this conversion for me, stripping the time of course? Result: 09/22/2003 "prod sorter" <prodsorter@discussions.microsoft.com> wrote in message news:9DD4A2BA-7A0F-4B83-A1DA-4E98674CAE6D@microsoft.com... > When I export a table using phpMyAdmin I get a date string of: > > 1064272434 > > Which equates to: > > 09/22/2003 07:13:54 PM > > Is there a formula for excel that would make ...

Numeric Overflow when importing text file into a linked table
I'm having problems when importing a text file into a linked table? Can somebody help me please? I don't have this kind of problem before when my table is not linked meaning it is in the same location where my front-end resides. The problem occurred when I split the backend and the front-end. Here is the structure of my table: Field A text Field B text Field C text Field D Double -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200801/1 Hi, would you post some details please. --example of rows that ...

convert to date format
hi all, i have exported an word table to excel. in one column of the word file date was written in the form of dd.mm.yy. now when i copied the the table into excel, the excel can't read the date in dd.mm.yy. i need excel to convert them them to dd/mm/yy format and treat them as date. is that possible? mosaddeq After the import, select the column of dates and then a simple CTRL+H, Replace All to replace "." with "/". That should convert the dates on- the-fly. I'd check this after the conversion. I use mdy order and I'm not sure if dates like 01.02.03 wo...

Attachments convert to text format
When I send a message with an attachment in Outlook XP some receivers claim that all they receive is a text mail. My default mail format is plain text. If I change it to HTML I fear some recivers would not see the content at all. How can I solve it ? Note: I change the format of a particular mail if it contains an attachment, but sometimes I miss to do that. Tork2001 wrote: > When I send a message with an attachment in Outlook XP some receivers claim > that all they receive is a text mail. > My default mail format is plain text. If I change it to HTML I fear some > recivers ...

Can I convert columns to rows?
I need to convert address information, which is on word and listed vertically, to an excel spreadsheet, and have the information list out horizontally. Is this possible to do? I am familiar with the basics of each program, but using "range,formula,value" options in excel confuses me. Here's an example: (My data in word) Mel's Tire Store, Inc. 300 Culbertson Avenue Worland, WY 82401 (307) 347-3601 Need to copy and paste so data spreads horizontally in Excel, under these colum headings: "Business Name" "Address" "City&qu...

Word equation too large to convert error
I am using field codes to program Word and it is working OK in all Word versions other than Word 2007. Even the native field codes with EQ created with Word 2007 itself giving blank equation upon double clicked with the above error. Is there a solution for this bug? My clients who are users of the document generated are getting frustrated with this sudden new behaviour of Word 7. CAn any one know the reason and solution for this. Thanks in advance Hi PSPC, You'll get an error if the equation spans more than one line, so make sure it doesn't. BTW, this affects all Word ...

"Publisher cannot convert this picture"
I get this error message when I try to insert or paste jpeg images into Publisher 2000. The images load and view in the Insert Picture dialog box when I select either All Picture Formats of JPEG File Interchange Format; however, the filenames do not appear in the File name: window when I highlight them in the list above. Every .jpg image or only certain ones? From where are you copying the images, the Internet? If your temporary Internet cache is full the copy will fail. In Internet Explorer, tools, Internet options, delete files. Joint Photographic Expert Group (JPEG) files are extreme...

Convert Publisher document to a .pdf file
I create a multi-page newsletter using Publisher. How does one convert this to a single .pdf file? My first attempts (copy/paste into Acrobat) only produce page 1. Is there a way to capture the whole document which includes photos and clipart so that I can paste it into Acrobat? Thanks, EdC. You don't paste into Acrobat, you print the document to Acrobat. File, print, select the distiller or Adobe PDF, depending on the version of Acrobat. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Ed C" <econgleton@snet....

Built in Excel 2003 need to convert to 2007
I have built a spreadsheet in 2003 with macros, advanced filtering and vlookups. I open it 2007 compatibility mode, but have found as I develop the spreadsheet further I am being warned of more compatibility issues. I need to be able to use all the 2007 features to move forward so how do I convert what I already have from 2003 to 2007? Is it as simple as changing the Save As format to Macro Enabled Workbook? I don't want to re-create everything. Thanks! xl2003 should be able to be forward compatible whereas 2007 may ?? NOT be backward compatible. You should?? be able to ...

pie-charting non-numeric data
I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value...

Converting files from 2007 to 2002 programs
-- I was a beta tester for MS Office 2007 and the test ended 02/01/2007. I spent six months as a tester and did a lot of work on my website with program. When the beta test program ended, I had to reinstall MS word 2002. Now the major problem is not being able to convert the files prepared from Word 2007 back to Word 2002. I go to open my saved files and this is what I come up with: "The file was created with a newer version of MS Word. Do you want to download a compatability pack so that you can work with this file". I go ahead and click the "OK" button. I com...

Convert old Q&A database to access
Does anyone know a free way of converting an old DOS based Q&A .dtf database to Access? There are only 6 fields: first name, last name, date of birth, date of death, cemetary, war if any. This is for a non profit that has been building a cemetary database for years and need to get something modern. I will build the Acces piece for free. But if I can't convert the data for free the project is dead. There may even be an export option within Q&A that would help. I didn't have enough time to look at it so far. thanks in advance, Mark Can Q&A export to .CSV (or any...

Convert Text to Number on 4,508 excel files.
Hi all. I'm faced with a rather unique problem.I have a large amount of improperly formatted excel files. They all have various cells formatted as text, yet they contain numbers and perform calculations on the cells. I now need to get these in a proper state. What I need is: Conversion of all text boxes containing numbers over to proper format of number. Do it automagically. I don't relish the thought of opening each one individually and fixing it. Any thoughts, tips or ideas on this? It could possible if you process that works by Scripting . In Scripting engine 5.6 Filesystem...

Numerous fsa*.tmp files being generated
I've noticed a large quantity of fsa*.tmp files being generated in folders on our W2K Server. We use MS Office apps only. Is there a setting on the server or within MS Office that can be changed to prevent this enormous clutter:( There have been a few posts about excel creating .tmp files in the same folder as the workbook file. I've never seen excel do this by itself. (It may create some .tmp files in your Temp folder, though.) I've never seen a good resolution to this, either. I always guessed that it was another program "helping out"--maybe your antivirus s...

Excel changes file name to numeric name when saving --HELP
When DFS is turned on, the name changes to a numeric name that includes date and timestamp. Enviorment Win2K SP4, replicating to a Win2k3 server. When Excel saves a file, it first saves it under a temporary name. If there are no errors during the save, it deletes the original file and renames the new version. Sounds like your network settings/permissions are not allowing Excel to delete the original file. On Fri, 4 Feb 2005 07:57:06 -0800, Greg Rinaldi <GregRinaldi@discussions.microsoft.com> wrote: >When DFS is turned on, the name changes to a numeric name that includes date >...

Quattro Pro Converter #3
Does anyone know where.how to get a converter from Quattro Pro 5.00 to Microsoft Excell 2002? Thank You, Bob Runkel P.O. Box 2826 Monterey, CA 93942-1816 Email: brunkel@acm.org ...

Converting selected section of text to numbers
Could someone please help me to extract some details from "1/10/2004 through 31/01/2005", which has been pasted into one cell. I need a formula to extract the month, 10 and another formula to extract the number 01 I then need to convert those calendar months to fiscal units (or fiscal months), so 10 becomes 4 and 01 becomes 7. Please note that these dates change and may not always have the same number of characters. ie 1/10/2004 could be 11/10/2004, etc. I need to be able to then do a calculation on those 2 fiscal units (eg 7-4=3). I hope that's clear. Rob presume you...

converting from works database
I've got old works database files (.wdb) on backup disk that I would like to import/convert into Excel 2000. Newer computer, old works program not loaded on this machine. So, I can't do as Microsoft suggests and open in works and saveas dbase file. Please advise. Thanks in advance! ...