Database text converted to numeric

I Imported data from a SQL Database, it is numeric and alpha numeric but
mostly numeric numbers (Serial numbers). I need to compare this list to
another Excel list containing the same information but with some
numbers missing. I pull this into Access and when I try and compare
them the error is that the format has to be the same.

I changed the format of the list I imported to Numeric (most of the
numbers is only numbers) but now I have to press F2 and enter on every
single number (database is only 227500 lines) to convert it to a
Number.

I thought it was because it still saw itself as a Database value so I
copied it into a new workbook but it's still happening.

Is there any help out there please!!


-- 
Estie
0
4/20/2005 2:07:40 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
578 Views

Similar Articles

[PageSpeed] 59

Try this macro from David McRitchie.

Look for TRIMALL, it's about half way down the page:

http://www.mvps.org/dmcritchie/excel/join.htm

Carole O

"Estie" wrote:

> 
> I Imported data from a SQL Database, it is numeric and alpha numeric but
> mostly numeric numbers (Serial numbers). I need to compare this list to
> another Excel list containing the same information but with some
> numbers missing. I pull this into Access and when I try and compare
> them the error is that the format has to be the same.
> 
> I changed the format of the list I imported to Numeric (most of the
> numbers is only numbers) but now I have to press F2 and enter on every
> single number (database is only 227500 lines) to convert it to a
> Number.
> 
> I thought it was because it still saw itself as a Database value so I
> copied it into a new workbook but it's still happening.
> 
> Is there any help out there please!!
> 
> 
> -- 
> Estie
> 
0
CaroleO (115)
4/20/2005 7:30:05 PM
Estie

Simply changing the format will not do the trick if the "numbers" are seen as
text.

Copy a blank cell formatted as General.

Select your "numbers" and Edit>Paste Special>Add>OK>Esc.

BTW, how do you get 227500 "lines" on a sheet?


Gord Dibben Excel MVP


On Wed, 20 Apr 2005 15:07:40 +0100, Estie <Estie.1nsv83@news.excelbanter.com>
wrote:

>
>I Imported data from a SQL Database, it is numeric and alpha numeric but
>mostly numeric numbers (Serial numbers). I need to compare this list to
>another Excel list containing the same information but with some
>numbers missing. I pull this into Access and when I try and compare
>them the error is that the format has to be the same.
>
>I changed the format of the list I imported to Numeric (most of the
>numbers is only numbers) but now I have to press F2 and enter on every
>single number (database is only 227500 lines) to convert it to a
>Number.
>
>I thought it was because it still saw itself as a Database value so I
>copied it into a new workbook but it's still happening.
>
>Is there any help out there please!!

0
Gord
4/21/2005 12:19:12 AM
Reply:

Similar Artilces:

empty text box
I am brand new to Access (a Paradox user) and am bewildered by the Text Box issue. I have a report in which I want to add a page footer that states the report is confidential. When I try to go to print view, it asks me to enter a parameter value. this is so frustrating, please help. Thanks! That would not be the problem. It is asking for parameters for the query that is the report's record source. -- Dave Hargis, Microsoft Access MVP "peabody" wrote: > I am brand new to Access (a Paradox user) and am bewildered by the Text Box > issue. I have a report in whi...

Access Database Conversion to Excel Database
I need to convert a downloaded database in Access format to an Excel format I can use on my desktop. Hi Greg One way Use Data>Import External Data in the menubar -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "greg" <anonymous@discussions.microsoft.com> wrote in message news:b0f301c3ec2b$2b12d830$a001280a@phx.gbl... > I need to convert a downloaded database in Access format > to an Excel format I can use on my desktop. ...

Nothing shows up in Database objects in Database Wizard
Hello, I am having touble with Visio 2003 once again. Though the db wizard, I created an excel spreadsheet (table) for data and am tryinig to link the chart back to this original database so that when I change something in the database it will change correspondingly. I am going back through the DB wizard, and can only get as far as selecting a database object to connect to. I have selected the original dada source, checked table, but nothing showes up under the database objects. Am I or my computer missing something? Please help! Thank You. Have you defined a name for the region ...

Huge database
Good afternoon, i have to make a huge database wich have to get data from excel file.That is not a big problem problem is that i have to get information for about 1000 families with 1000 elements for every family and this database have to storage this information at leaset one year. This make a huge pack of elements. Can Microsoft Access get so huge pack of data ? If yes i would be glad to read some ideas. Thanks If you are talking about 1000 columns in a table, Access can't do it. 255 is the upper limit. 1,000 * 1,000 is potentially a million records. However if the data is pr...

linking 2 databases in 1
Hi, I am running 2 hqs for 2 different purposes, now the company decided to use 1 hq for both purpose. How can i link the 2 databases into 1 database???? I tired to link them from SQL server enterprise manager-all task- export data from database A to B but some tables can not be exported and show me tables failure, is it possible to link them or im just trying something impossible? Advice me on that plZzzzZZzz -- Aliko ...

Pasting large amounts of text into a cell
When pasting large amounts of text (using Paste Special - Text) into a cell, we don't see all the text. Some text appears to run beyond the cell border. Tried the AutoFit and that doesn't make the text fit. When I widen the column, the text still doesn't appear properly. What more can I do? Thank You. Ardell Ardell Excel Help on "limits" or "specifications" reveals that Excel will allow 32,767 characters to be entered in a cell. However, it goes on to state that "only 1024 characters will be visible or can be printed" To work around this...

Importing Current Database into CRM Database
Hi, I would just like to know if it is possible to tranfer my current database into the Adventure Works Database(and if so how) or is it used just for a few records for testing purposes. You help will be highly appreciated... there is a redeployment tool on support.microsoft.com/downloads under microsoft crm. This may help you. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Rock619" <rodger@intervigil.com> wrote in message news:5728edd40161d2cca1c0e079cfd86665@localhost.talkaboutsoftware.com... > Hi, > > I would just like to know if it is pos...

how to start a database
hi guys, i want to start a database on excel 2000, i never had to do this before, and i know this can be done. i just dont know where to start from ! what i want to do is, it is a racing club, i want a listing of all pilot and a sheet where we can enter the number of the car(all pilot info need will be transfer) for final result of the race.(right now they enter the car no, name , city at every race at every registration, if he is register to 3 race they do the process 3 time.) like in access went we create the database and after the query, that what i want to do in excel. any tip or link...

Use query from another database
Hi In the code below is there a way that qryOrderHistory could be in a separate database. If so how would i refer to it in the code. Thanks Dim db As DAO.Database Dim qd As DAO.Querydef Dim rs As DAO.Recordset Set db = CurrentDb Set qd = db.QueryDefs("qryOrderHistory") qd.Parameters("prmCustNo") = lngCustNo Set rs = qd.OpenRecordset() If Not rs.Eof Then .... code to populate an excel spreadsheet Change your 'Set db = CurrentDb' line to something like the following .. Set db = DbEngine.OpenDatabase("full path and name of target MDB here") -- ...

Database Queries with database views, that base on many database t
I created 2 database views. Each of them uses 256 database tables. From every database table only one column is used in the select list of the view. Every database table has one column which is used for the join. Each database table has 5 rows. The database tables have not any foreign keys and indexes. Both views works fine. When I use the database views in a query like this: SELECT v1.*, v2.* FROM v1, v2 WHERE v1.id=v2.id I should get 5 rows with 512 columns in the resultset. But I get the following error from the SQL-Server (after about 5 minutes): Msg 8621, Level 17, State 2...

first letter of text box referece
i have created a user input form in VBA, which works fine. "organisation" is a field on the form if a user enters an organisation beggining with letters A-F for example, i want the data entered to go into sheet 1. if the organisation begins with G-L, i want it to go into sheet 2 etc... i have managed such a formula in excel, but does not work in VB. my current attempt looks like this: If Organisation.Value(Left >= CHAR(65), Left <= CHAR(70)) Then ActiveWorkbook.Sheets("sheet1").Activate Range("A2").Select ElseIf Organisation.Value(Left >= CHAR(71)...

full text search
Hello, It does not look like vista is full text indexing word documents. I have set the option: Indexing Options in the Control Panel and click Advanced, then clicked the File Types tab. Add the .doc, doxc file types and clicked the option to "index properties and file contents." However I still cannot full text search word document. any ideas? thanks The answer to Vista's lame search is: www.mythicsoft.com FileLocatorPro. There's also free AgentRansack but like most free things, it's not superb. WindowsXp search was slow but WORKING, especially...

Impact of changing database column width?
Hi, We are a software product company and are planning to begin using the Contract Administration module. To do so, we will be switching our inventory items from Track: None to Track: Serial Numbers and we will be storing our software license keys in the Serial Number field which is column SERLTNUM in table SOP10201. Unfortunately, SERLTNUM is CHAR(21) and our license keys are a few characters wider than 21. We can get the keys down to 21 characters by removing three embedded hyphens, but there are several databases outside of GP where the license keys must still exist with...

Set Database in Excel
In Microsoft article 183446 under method 2, it refers to using "Set Database" on the Data menu. I am using Excel 2002 (the article says it applies to Excel 2002), but Set Database does not appear in my Data menu. Is this an error? It's an error due, I think, to very old menu structures. The article says that the information applies to everything from version 5.0 (1995) through 2003, inclusive. But I believe that the Set Database menu item disappeared from the Data menu as of Excel 97. And one has long defined a name using the Insert menu, not the Formula menu. But my rapidl...

Client access problem after databases restoration
Hello! Recently we had a problem with an Exchange 2003 Back-End server with 6 mailbox store distributed thru 4 SG, and we lost the databases because the SAN's disks partitions were deleted by mistake. So, we had to do a restore from a backup, but when we could not mount the stores, so we used eseutil /p command, and after, eseutil /d, and then isinteg -s servername -fix -test alltests. We could mount the stores, users can use email. The problem now is that we have some users that could not access their mailbox thru Outlook, and when we try to move to another store, we receive an ...

Text unwaps with large amounts of text
When I type large amounts of text into a cell, after about 12 lines or so the text no longer wraps itself. I cannot figure out why this is happening and have not been able to find a solution. Can anyone provide any input? Thanks in advance. could be a character limitation per cell that you're running into. Just a hunch though. hth Kevin McClement >-----Original Message----- >When I type large amounts of text into a cell, after about >12 lines or so the text no longer wraps itself. I cannot >figure out why this is happening and have not been able to >find a solutio...

upgraded from MSSQL 2000 to MSSQL 2005 now database's users have
Hello. I updated the MSSQL 2000 server to MSSQL 2005 server now the database's users have no login names. I found a way to list orphaned users sp_change_users_login 'Report' but it only listed dbo and there are actually five listed with MSSQL Server Management Studio Express. I tried this sp_change_users_login 'update_one', 'RPS', 'RPS' but I get the following error the user name 'RPS' is absent or invalid. Any ideas? Try with Auto_Fix instead; see the example at the end of the following article: http://msdn.microsoft.com/en-us/...

"Convert" Driver vom XP64 to Win7 64
Hallo NG, I'm not the Manufacturer of the Device and I don't have source-access. So if there is a working driver for Windows XP 64 Bit Edition, is it somehow possible to get it installed on Win7 (with test-signing)? Can it be achieved by modifing the .inf? GP I think it will install with a question of "Do you trust this = software?" --=20 Maxim S. Shatskih Windows DDK MVP maxim@storagecraft.com http://www.storagecraft.com "G=EF=BF=BDnter Prossliner" <nospam@spam.com> wrote in message = news:%23pcCh162KHA.1452@TK2MSFTNGP06.p...

Hide text in formula bar
I am using excel 97. I know it is possible to hide the edit or formula bar, but I wan tit visible, just want the cell contents not to show up in it. I konw that "hidden" option will work, but I don't want to protect the cells. Is there anyway to have some cells not show up in the edit bar without using protection? thanks, Billy Billy, If it's formulas you don't want shown, you can format the cells (Format - Cells - Protection - Hidden). Then protect the worksheet (Tools, Protection, Protect Sheet. If that isn't it, explain why you want a formula bar, but don&#...

database or other software?
I need help with the following situation: I want to create files or a database that will contain information about my clients, with their business information (i.e address, phone, fax, type of business, in which states they do business) to send an invitation to bid on a construction project. I want , where a database would fill out the information automatically on the bid invitation, then fax, or email the invitation to each selected clients according to their specialty. Then I will need to keep track of who answers my invitations in order to "weed out" clients who do...

Form in another database
How can I check if an Access form is open or loaded when the form is in another Access application (mdb or nde file). Alex ...

Automated HTML reply to plain text email
Hi, I am receiving a lot of emails with the same subject line. They are all plain text emails and i would like to set up a rule that replies automatically using a HTML coded template. For some reason this is not working as normal a normal reply rule would work. any suggestions please? Many thanks Max. Submitted using http://www.outlookforums.com What exactly happens when you try? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-sub...

Do we have a software which can export text to excel perfectly?
do we have such software frnds??? -- Message posted from http://www.ExcelForum.com Do you mean Import into excel? And if you can tell excel that the data is either fixed width or has a common separator, then excel will be pretty close to perfect! If you can't tell excel that stuff, then I think you'll need to do some data cleansing after you import it. "vikram <" wrote: > > do we have such software frnds???? > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com ...

convert numbers to time
I was working in Excel and typed in my time values as numbers (military time), exported the data to Access and now would like to convert my numbers to hrs:nn. Any suggestions would be greatly appreciated. chmcrae Access does not have a "time" data type. Access does have a Date/Time data type. If you only have time, consider storing that data in Access as Text, then coming up with procedures that do the conversion/formatting. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Me...

Attendance Database Setup
I am creating an attendance database for my company. There are 19 different departments in the company and each manager tallies his/her own employee attendances. I had a spreadsheet set up, but it wasn't on a rolling calendar basis. Then, I was going to have the managers delete old months, but that would most likely mess with the formulas. So the new spreadsheet needs to be on a rolling calendar basis, so that when a new month comes up, the previous month from last year will drop off in calculations. My file has three worksheets, one titled "Summary", one titled "Detail...