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
806 Views

Similar Articles

[PageSpeed] 33

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:

Referring to a field of a database of other sheet
Dear All How can one refer in sheet2 to the name of a field of a database placed in sheet1? Thanks in advance, Paul If I understand your question, the answer should be: =Sheet1!A1 (adjust A1 range to your needs) HTH -- AP "Paul Smith" <phhs80@gmail.com> a �crit dans le message de news:1141597411.722483.209810@p10g2000cwp.googlegroups.com... > Dear All > > How can one refer in sheet2 to the name of a field of a database placed > in sheet1? > > Thanks in advance, > > Paul > OK, but then why does not the following work fine as an advanced fil...

use CSV as Database Files to generate Crystal Report
Hi all, I am trying to use CSV file as Database Files to generate Crystal Report. However, I am always prompted for user and password, which does not make sense. However, it works well with XML file. Could someone please help me on how to get the CSV file to work? Thank you very much. regards, Sean You'd be better of posting to a newsgroup regarding Crystal. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Tue, 23 Nov 2004 08:44:26 -0800, "Sean" <anonymous@discussions.microsoft.com> wrote: Hi al...

Splitting a database
Hi, I have broken my database into several stages. I have implemented the first stage and this is simply the database (with basic features) on a central computer and a shortcut added to each user's computer. I know this is quite risky but at this stage only one person is cupturing and editing the data and the rest is viewing information and printing reports. I am still building on new features of the database and this would include adding more fields to some tables. 1 - Could i now split my database and give each user a FE, given the fact that i will still be adding and removin...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

how to preserve paragraph breaks in pasted text?
When I paste text copied from a Word document into an Excel cell, Excel breaks the pasted text into separate cells according to the paragraph breaks contained in the pasted text. However, I would like to preserve those paragraph breaks and keep all the pasted text in the same cell. I realize that when entering text in a cell, I must insert Alt+Return to get a paragraph break to stay in the cell. But there doesn't seem to be a way to force pasted text to stay in a single cell when it contains paragraph breaks. Any advice would be most appreciated. In Word, change the hard returns ...

works database to access database
I have a colleague (at a distant location) who is using the Works database feature and is trying to develop a small database. She would like me to look at it and assist, but I only have Access 2000. Is there anyway I can read her files and still leave her files in a format that she will still be able to read when it loaded back on her machine? Many thanks Marion I don't believe you can unless you have MS Works. "Marion" <anonymous@discussions.microsoft.com> wrote in message news:e5ad01c3f12b$733385b0$a101280a@phx.gbl... > I have a colleague (at a distant lo...

Best Practise DPM database local or remote
What's best practise for placing the DPM database. If your placing it on a remote sql server, and the remote server crashes, your DPM server also has no DPM database. So making a restore of your remote server or getting DPM server back to work will be a hell of a job. And by placing the DPM database on the local DPM server, it's of cause easier to restore a crashed remote server, but it will still be labourous to restore a crashed DPM server, if the DPM database is corrupted of even gone. So what I would like to know is, if it's better to install the DPM database ...

Importing text from a website
Hello. Nursemaiding required for a perpetual lamer please. I want to put this stuff on an XL sheet or in another producer's spreadsheet if it will be easier. I wish to move the date over to the other side of the month and put the hour column in its own cells separate from the minutes as I wish to divide the hour (by 6) without touching the minutes. It must be a simple process I suppose but I wouldn't know where to start. Thank you in advance etc., etc.. http://aa.usno.navy.mil/cgi-bin/aa_moonphases.pl?year=2006 2006 Phases of the Moon Universal Time 00000 New Moon 000 First Q...

Text Area indicator Word 2007
When I installed MS Office, I set the word 2007 page to indicate the text area (doted lines) on 4 sides of the page. Wish to remove them now, but don't remember where it was that set them Help PLEASE TIA Word Options > Advanced > Show Document Content > Uncheck 'Show Text Boundaries" -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><&l...

Search for text strings with multiple conditions
Hello! I want to search for the word "Hello" in columns which can consist of "Hello;Hi;Greetings" and want the hello must based on several conditions. Now I use this formula, but then I can=B4t find the cells which contains more text than just "Hello": =3DSUM(IF(BP33:BP1000=3DBP8;IF(BR33:BR$1000=3D"Hello";1;0))) I want the formula to be like this: =3DSUM(IF(BP33:BP1000=3DBP8;IF(BR33:BR$1000=3D"*Hello*";1;0))) But the "*Hello*" doesn=B4t work...does anyone know a formula which is very similar to the one above but can search throug...

trouble sending/forwarding mail in RTF or plain text via VPN
While using a VPN on a wireless network, Outlook functions fine EXCEPT that I cannot send/forward mail that is not in HTML format. When doing so, I only get the message that Outlook is searching for my corporate mail server. Other functions seem to work fine. Any clues?! Thanks-- ...

alternate quoted text formatting?
Is there any way to change Outlook's behaviour vis-a-vi quoted text in replies. It uses vertical bars to indicate quote level, but these just don't show up (in any consistent way) when viewed in any non-Outlook mail client. I'd much prefer Outlook to use ">" for quoted text or at least some method that is not limited to viewing in Microsoft clients. The only way I can find to do this is to use the view all mail as plain text option, but then if I'm forwarded an e-mail from an Outlook user where there is already quoted text in the vertical bars style, these are jus...

Database Size Limit and Old Mailboxes
I have a two quick questions if anyone has any answers that would be great. 1) I have upgraded my Exchange 2000 Standard (16GB Database Limit) to Exchange 2003 Standard. Is the database limit still the same 2) I have deleted some ex-employee accounts and mailboxes but I am still having people email these addresses. Is there a way to assign an autoreply short of recreating the accounts and setting up out of office? Currently the mail is being forwarded to a receptionist Thanks Darix 1. Yes 2. So do you want the ex-empolyee mail to stop all together? If not, you could setup a Moderat...

I would like to convert my dollar totals into a bar chart format
I am attempting this for the first time and would appreciate help. Thank you. EXCEL 2007 Hi Shirley, please go to:- http://www.pierrefondes.com/ Item number 108. Follow the intructions starting in cell K 1 to create a Bar Chart (it's very simple and only takes a minute or two). Then, change the underlying data in cells A 1 to B 10 to match your needs. This should give you what you want. If my comments have helped please hit Yes. Thanks. "shirleyweston" wrote: > I am attempting this for the first time and would appreciate help. Thank yo...

Save workbook as a Text file
Hi, I have recorded a macro to store an Excel workbook as a .* txt file. The result from the macro-recorder is mentioned below --------------------------------------------------------------------------------- ActiveWorkbook. SaveAs Filename:="C:\Temp\map1.txt", _ FileFormat:=xlTextMSDOS, CreateBackup:=False --------------------------------------------------------------------------------- If I carry out above action manually the text is taken over exactly the same as in the workbook, however if I carry out the action by using the above a macro, some data with "quotations ma...

transferring data/text to another worksheet
I have two columns/rows with the following data on Sheet1: Column: A B Row: 1 178 buy 2 buy 3 185 buy 4 140 sell 5 sell 6 sell 7 130 buy I want to tranfer to sheet 2, all the cells in column A that have number value and if column A has a number, then transfer the text i column B. This is what I want sheet 2 to look like: ...

track changes text shows on screen does not print
tracked changes (of an imported excel graphic) shows on screen in final view but does not print. I accepted the changes and it still shows on screen but does not print. (1) In your Print dialog, you probably have a "Print What?" menu. Change it to "Document with Markup." (2) If you've Accepted the Changes, they should not still show on screen. Are they changes made in Excel that need to be Accepted in Ecxel? On Mar 31, 12:42=A0pm, Hyphenation <Hyphenat...@discussions.microsoft.com> wrote: > tracked changes (of an imported excel graphic) shows ...

How can I display a cell range in a text box in excel?
I want to display the contents of a cell range in a text box. I know how to put contents from one cell in a text box but I need to put the contents of 24 cells in my text box. Or how can I change the length and height of cells below other cells without changing the other cells length and height. Is there a way to put a line to freeze panes to change the length and height of cells below the line? Would the '&' work for this problem. eg if A1 = "Pat" and B1 = "Hughes" then the formula A1&B1 would return PatHughes, formula A1&" "&...

Printing Text Boxes with a chart
Hi - sorry if this is a no-brainer but I can't get it to work I make a chart from figures on a worksheet - easy Then I add some text boxes on top of the chart to comment on specific features etc Then I go print and select "selected chart" - the printer prints the chart but not the text boxes on top of it How can I print the chart with the text boxes caontaining comments Thank DR Dave - When you select the chart, do the text boxes vanish? That's because they are not part of the chart. Select the chart first, then add your text box. In fact, just select the chart and star...

Convert Quicken 2006 to MS Money 2006 Trial version
I am currently using Quicken Premier 2006. I downloaded the trial version of MS Money 2006. However, it states that you can only convert data from Q2005 and earlier. When I tried to convert Q2006 into Money, it wouldn't do it, saying it was unable to convert from this version. Any resolutions to this? I would really like to try the trial version of Money. Not converting the data doesn't prevent you from using the Money trial. You'll just have to start from scratch--presumably you had to do this with Quicken as well. If the past predicts the future, M07 will convert Q06...

Publisher 2000 & web pages & text on bmp's
I have a web site for my software I sell. A couple pages contain BMP pics of a few screens & a couple reports. When I am in the design mode the text on these bmp's is relatively clear but when I PREVIEW the pages with these bmp, the text is almost illegible. I have tried to insert various objects but with NO LUCK. Can anybody offer me some suggestions of what to do? Thanks. DeWayne to auto reply remove the x from my address below dchiasxs1@rogers.com web pages support gif or jpg formats. Pub would convert the bmp to gif which could effect resolution. I recommend you create your im...

Convert data in Excel file to vsd format
H We are dynamically building an Excel sheet aith data to be shown as an Organization chart How can we convert this file into vsd format from Visual Basic Also, how can we show the Excel file contents on the WEB using asp Really Urgent. Regard Ajith well, I'm looking at Office2003 and Visio2003. Visio has the OrgChart Wizard for import of excel data, and Excel has the export of html. I believe this is functional back to the 2000 version as well. What version of the tools are you using? Al "Ajitha Anil" <anonymous@discussions.microsoft.com> wrote in ...

How to convert two dates in to HH:MM:SS
Hi Guys, I am trying to calculates the two dates cells diffrence int hour:minutes:seconds. could some help me in finding a solution i Excel sheet. eg. Cell A1 = Jan-01-2004 00:00 Cell B1 = Jan-02-2004 23:30 Cell C1 = 47:30 looking for a result like 47 hours 3 min. thanks sa -- sashaik ----------------------------------------------------------------------- sashaikh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1555 View this thread: http://www.excelforum.com/showthread.php?threadid=27118 Subtract the two cells and use the Custom Format ...

Point labels in an Excel scatter plot to be associated text
I am trying to plot a scatter plot where I would like each point to have a specific name. For instance, I have three columns, company name, total revenue, and % margin. I would like to plot revenu vs. % margin and when clicking on a specific point, I would like to find out the company name. Check out Rob Bovey's Chartlabeler at www.appspro.com or John Walkenbach's Chart Tools at www.j-walk.com. If you want to see the labels only when you hover the mouse over a point see Hover Chart Label http://www.tushar-mehta.com/excel/software/chart_hover_label/index.html Note that the add-...

Converting from MSPOS to RMS
I am trying to convert my database from MSPOS to RMS. Is there any way to do this I am getting server failure errors when I try to go into RMS upon doing this. There is no official upgrade path at this time. Contact your reseller for help or check out http://www.retailrealm.us. Retail Realm has an import utility that should allow you to get most if not all of your data converted over. I've heard that they also have something specific for POS -> RMS, but I don't have any other info about that... Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.co...