Number Format #10

Hello
I've got a large spreadsheet which was imported. Apart from the first 2 
columns, the rows consist of number which I need to filter, but this isn't 
working. Then I noticed an apostrophe before each number in every cell, 
which doesn't show in the cell but up in the formula/text bar.
I've set all the columns to numbers but the apostrophe won't go away.
Please can someone tell me how I can without having to go into each cell as 
there are hundreds.
Thank you
J 

0
JB
11/11/2008 11:24:33 PM
excel 39879 articles. 2 followers. Follow

4 Replies
570 Views

Similar Articles

[PageSpeed] 11

Select all the columns with these "numbers" in them, then right click the 
tab at the bottom of the worksheet and select View Code from the popup menu 
that appears, then copy/paste this statement into the Immediate Window (if 
you don't see the Immediate Window, press Ctrl+G) and, with the text cursor 
somewhere on the statement, hit the Enter key...

Selection.Formula = Selection.Formula

-- 
Rick (MVP - Excel)


"JB" <somehow@somewhere> wrote in message 
news:%23M7y1SFRJHA.1164@TK2MSFTNGP03.phx.gbl...
> Hello
> I've got a large spreadsheet which was imported. Apart from the first 2 
> columns, the rows consist of number which I need to filter, but this isn't 
> working. Then I noticed an apostrophe before each number in every cell, 
> which doesn't show in the cell but up in the formula/text bar.
> I've set all the columns to numbers but the apostrophe won't go away.
> Please can someone tell me how I can without having to go into each cell 
> as there are hundreds.
> Thank you
> J 

0
11/11/2008 11:56:31 PM
Hi,

Click a blank cell and copy it
Select all the cells that should be numbers and choose Edit, Paste Special, 
Add.

Done.

If this helps,  please click the Yes button.

"JB" wrote:

> Hello
> I've got a large spreadsheet which was imported. Apart from the first 2 
> columns, the rows consist of number which I need to filter, but this isn't 
> working. Then I noticed an apostrophe before each number in every cell, 
> which doesn't show in the cell but up in the formula/text bar.
> I've set all the columns to numbers but the apostrophe won't go away.
> Please can someone tell me how I can without having to go into each cell as 
> there are hundreds.
> Thank you
> J 
> 
> 
0
11/12/2008 5:20:01 AM
Coooooooooool. that was fun and worked a treat
Ta

"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:u0PLgkFRJHA.1028@TK2MSFTNGP05.phx.gbl...
> Select all the columns with these "numbers" in them, then right click the 
> tab at the bottom of the worksheet and select View Code from the popup 
> menu that appears, then copy/paste this statement into the Immediate 
> Window (if you don't see the Immediate Window, press Ctrl+G) and, with the 
> text cursor somewhere on the statement, hit the Enter key...
>
> Selection.Formula = Selection.Formula
>
> -- 
> Rick (MVP - Excel)
>
>
> "JB" <somehow@somewhere> wrote in message 
> news:%23M7y1SFRJHA.1164@TK2MSFTNGP03.phx.gbl...
>> Hello
>> I've got a large spreadsheet which was imported. Apart from the first 2 
>> columns, the rows consist of number which I need to filter, but this 
>> isn't working. Then I noticed an apostrophe before each number in every 
>> cell, which doesn't show in the cell but up in the formula/text bar.
>> I've set all the columns to numbers but the apostrophe won't go away.
>> Please can someone tell me how I can without having to go into each cell 
>> as there are hundreds.
>> Thank you
>> J
> 
0
JB
11/14/2008 7:00:59 PM
Nice! that worked too
Thanks

"Shane Devenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in 
message news:CF294F9C-A93E-4DD2-971E-EB97B4584D6E@microsoft.com...
> Hi,
>
> Click a blank cell and copy it
> Select all the cells that should be numbers and choose Edit, Paste 
> Special,
> Add.
>
> Done.
>
> If this helps,  please click the Yes button.
>
> "JB" wrote:
>
>> Hello
>> I've got a large spreadsheet which was imported. Apart from the first 2
>> columns, the rows consist of number which I need to filter, but this 
>> isn't
>> working. Then I noticed an apostrophe before each number in every cell,
>> which doesn't show in the cell but up in the formula/text bar.
>> I've set all the columns to numbers but the apostrophe won't go away.
>> Please can someone tell me how I can without having to go into each cell 
>> as
>> there are hundreds.
>> Thank you
>> J
>>
>> 
0
JB
11/14/2008 7:02:48 PM
Reply:

Similar Artilces:

Number Format Problem Prevents Proper Sorting
I have a long list of numbers that were exported from anothe application that I can't seem to format properly to sort in ascendin order. I have changed the format from General to Number and back wit no success. I'm sure that this is an easy fix, but I'm not seeing th answer. The third entry below (1103) should be toward the top of th list. Any ideas? 110272 110277 1103 110329 110338 11033 -- 65Stan ----------------------------------------------------------------------- 65Stang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1500 View this threa...

limiting the number of records to print in a subreport
Good afternoon: I created a report that contains a subreport... I would like this subreport to be limited to 20 records per page... Any idea on how to achieve this result? Thanks Michel Michel Khennafi wrote: >I created a report that contains a subreport... I would like this subreport >to be limited to 20 records per page. That kind of implies that the subreport details do not grow. If they did, what would be the point of a limit? I think you will need to create a sequence number to rank each record in the subreport's record source query. This can only be done if one...

Text Formatting Command is not available
I had this appear whenever someone tried to open a message in outlook. Internet explorer would not open webpages also. This is caused by a corrupt IE install but there is no way of reinstalling it that I know of. I simply put in the windows XP CD and did a repair installation and it fixed the problem. I searched everywhere for a solution but no one had one so I am posting what worked for me. ...

I'd like to conditionally format a cell based on today's date
I have a column that contains important dates, and I'd like to say make the forecolor red if they're 3 months away, blue if their 6 months away, etc... How do I do this? Look up "conditional formatting" in Excel help. -- David Biddulph "0to60" <holeshot60_nospam@yahoo.com> wrote in message news:%23mzT42nAJHA.4052@TK2MSFTNGP06.phx.gbl... >I have a column that contains important dates, and I'd like to say make the >forecolor red if they're 3 months away, blue if their 6 months away, etc... > > How do I do this? On Aug 20, 2:32=A0...

office 2004 (excel & word) afp file path issue on tiger server (10.4.9) #2
g'day, we have just changed over to an xserve (running 10.4.9, HFS+ with ACLs) and are experiencing the following problem with office 2004 excel & word files only - surprisingly powerpoint files are not affected: all excel and word files without spaces in either file- path or -name open just fine, however all files with spaces in file-path or -name generate the following error message... <img src="http://homepage.mac.com/daniel.schagemann/posts/EXCEL_1.png" alt="excel error message" /> for the above we used the following example file: -rwxr--r-- 1 daniel...

%s in Accounting Format
I am using Excel 2007. I want to have a column that lists percentages - but to still be able to use the accounting single underline correctly. When I manipulate the accounting format to _(*#,##0.00%_) the underlining is not correct. If I use the accounting format I cannot get "%" signs. Thanks. Does this work? _(* 0.00%_);_(* (0.00%) -- Regards, Peo Sjoblom "jadeB" <jbschipper@gmail.com> wrote in message news:a9dfe12b-3c2f-4302-ba49-2c2dd39240c2@33g2000yqm.googlegroups.com... >I am using Excel 2007. > > I want to have a column that lists percen...

Turn off automatic date format
I searched the site but did not find an aswer to my question. I a copying data from a web site that look like this: 27-40 25-40 8-57 12-53 I want the data to remain as it is shown above but when I paste int Excel, I get the following: 27-40 25-40 Aug-57 Dec-53 It appears if the text does not look like a valid date excel leaves i alone. But if it looks like a possible date, it formats it into a dat format. How do I paste this data and prevent Excel from converting i into a date format? Thanks, Ji -- jpkeller5 ----------------------------------------------------------------------- jpkelle...

Access 2007 file format detection through DAO?
Hi all! Not long ago I created a tiny EXE that scans files in a folder and detects the format of the files for some inventory and data consolidation. It works fine for mdb files (2000, 2002, 2003). But it cannot detect Access 2007 format. The EXE is written in Visual Basic 6 using DAO3.6. Below is the code I use, but whenever I encounter an Access 2007 (accdb) it fails at the first line ("3343: Unrecognized database format"). Is there any updates for DAO or any other way I can gather some basic database information from the file? Thanks! Set db = DAO.OpenDatabase(MyFilePathn...

Formating Issues
I have a Publisher 2003 Brochure in progress. I only need the first page of this two page document. I can find no way to save only the first page of this work. Additionally, I've inserted a text box for a list of items and want to divide it into two columns. Need some guidance on how this would be done. Much thanks for any assistance. Go to page two, Edit, delete page. Save with a new name if you want both files. Right-click the text box, click format text box, Text Box tab, columns button. -- Mary Sauer http://msauer.mvps.org/ "Maurice" <Maurice@discussions.micr...

Conditional Formatting on cell with Formula
I have added this code to a module on my Personal worksheet Function ISFORMULACELL(cell) As Boolean ISFORMULACELL = cell.HasFormula End Function Then on another worksheet, I enter this formula in conditional formatting: =ISFORMULACELL(A1) I expect any cell that contains a formula to be highlighted in yellow but it didn't happen. Can you please help me identify what goes wrong? Thanks! ...

dissect large number
How do I dissect a large number (one column) into four (columns) keeping their own identity. I have a software that download (chart of accounts) into excel but this number is conected with activities during the year, and I like to break down into the Assets, revenue, inventory, expenses. example 1111400000 (one column) into 1-1114-00-000 (four columns) "Assets". -- Dummy Workalcholic "Dummy" <Dummy@discussions.microsoft.com> wrote in message news:21AA6743-15F3-4B32-81C2-92DBC300941F@microsoft.com... > How do I dissect a large number (one column) into four (...

Get the value with the highest ID number
Hi, I have a table with for instance these 2 values: ID Employee Department 1 Jones, M. Frontoffice 2 Jones, M. Backoffice In a combobox the user types-ahead the name. When they choose a name the latest entered department name (the one with the highest ID-number) should also be entered in the department field. In the example that is the Backoffice. I'm planning to do this with code in the after-update event. Is it possible to use Dlookup for this? If that's not possible and I would have to use a query, how would I get the right deparment value of the record that has ...

how can i get rms transaction number in PCCharge batch?
...

Problems after System Restore 01-11-10
I don't see my original post, so here it is again. After a system restore (saving data), PC is still not functioning properly, slow and whirring noise from hard drive when not using the PC. It is a HP with Windows Media 2005. When exploring system tools I noticed that all the start up programs (95 pages) have duplicates. Is this normal? Also, PC is still running slow. Here is an example of what the start up programs show: $ncsp$ $ncsp$.inf NT AUTHORITY\SYSTEM Startup $ncsp$ $ncsp$.inf .DEFAULT Startup $winnt$ $winnt$.inf NT AUTHORITY\SYSTEM Startup $winnt$ $winnt$.inf .DEFAULT...

Conditional Formatting #60
Hi all I know this is probably something more suited to a database, but I want to try add this functionality into an existing spreadsheet I already use. I have a worksheet for each customer which contains contact details etc. What I have now done is included a section for notes & reminders and have used conditional formatting to show any overdue tasks and also to strike-out any completed tasks. What I would really like to do is as follows: 1) I would like a way to sort all tasks so that any overdue (highlighted in red) are pushed to the top of the list, with the remainder sorted in d...

converting number to string in hex
Hello there I have table with bigint values. I need to convert them to string as hex for example: 209 = C9 when i do select convert(binary(3), 209) it set on screen 0x0000C9 which is what i want. but when i try to enter it to string it set an empty string. Is there a way to enter "0000C9" to string? Here are two methods: http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx -- Plamen Ratchev http://www.SQLStudio.com On Tue, 27 Jul 2010 17:40:55 +0300, "Roy Goldhammer" <royg@yahoo....

Code number for Microsoft Office 2001
My iMac crashed about two months ago. In the process of rebuilding it, I removed Microsoft Office 2001 to free space for the rebuild process. Unfortunately I have lost the sleeve with the product code on it. What can I do? Please reply to wnlewis@southwind.net Wm. Neal Lewis In article <d61c01c3f00a$2f7c7290$a101280a@phx.gbl>, "Wm. Neal Lewis" <anonymous@discussions.microsoft.com> wrote: > My iMac crashed about two months ago. In the process of > rebuilding it, I removed Microsoft Office 2001 to free > space for the rebuild process. Unfortunately I have lost...

How to convert any number in text or word in excel?
Convert how? Into another number? Search and replace? What version of Excel? More details in the body of your message might give people a better clue of what you're trying to do. -- Susan Ramlet **please reply to the newsgroup so others may benefit** "formula" <formula@discussions.microsoft.com> wrote in message news:3975AD18-7608-45C8-A36C-8A382853CC60@microsoft.com... > ...

Exch 5.5 is stripping off html formatting
The only place I know of in exch admin I have html turned on.. But it is still stripping the html formatting off the email messages.. Does anyone have any type of solution or work around or something I'm not seeing.. thanks for your help. Scott And that one place would be...? "tazdog" <tazdog8@yahoo.com> wrote in message news:ebecdefc.0405110736.4e8f0bc0@posting.google.com... > The only place I know of in exch admin I have html turned on.. > But it is still stripping the html formatting off the email messages.. > > Does anyone have any type of solution or ...

Formatting Columns
Hello, I have tried and have not found an answer. Is there a way that I could arrange mail in a certain order, either by date or from, and have it take effect on all the folders and subfolders? I tried to customize fields, but still does not work. This is within outlook mailbox and also within personal folders, I am not able to do this. Any advice would be greatly appreciated. Thanks Sunil ...

how to delete Number/Custom format codes (2007)
I posted earlier but the term in Subject was wrong, and let me try again. In Excel chart Y axis, I created a format code. Some are by mistakes, and I want to delete them. I don't see any option beside Add there. How could I delete those unwanted format codes? According to Help (F1), I am supposed to be able to delete them from Dialogue box launcher for Number. I opened and select Custom but there aren't any format codes I created. Thx for your help. ...

Outlook 2003 Mail Merge Ltrs formatting greeting line
Hi, I'm merging contacts into a form letter but any name with a husband/wife (ex: John & Ann) shows up as "John &" after "Dear . . .". I inserted the "First Name" field there, so how do I indicate that I want that second first name there as well? I have thousands of letters to print and if I manually redo each incorrect one this will take me weeks! Thank you for helping me! ...

How do I print odd numbered pages
I want to print double sided. The even numbers on one side and the odds on the other. Is there a way to do this in Excel Maryjanet, check your printer settings there may be an option there to do it, if not here is a macro from Gord Dibben that will do it Sub PrintDoubleSided() 'Will print odd or even pages 'By Gord Dibben Dim Totalpages As Long Dim pg As Long Dim oddoreven As Integer On Error GoTo enditt Totalpages = ExecuteExcel4Macro("Get.Document(50)") oddoreven = InputBox("Enter 1 for Odd, 2 for Even") For pg = oddoreven To Totalpages Step 2 ActiveWindow.S...

Format text in Justify
I have inserted text into my newsletter (used every month) but this inserted text is stretching out the text of the last paragraph line (Widow?). How do I stop this. -- Frankly Speaking Turn on Special Characters ( � ), you may have a tab setting or it could be you did a shift+enter. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Frankly Speaking" <FranklySpeaking@discussions.microsoft.com> wrote in message news:4E0C9DAA-2235-4F04-8976-EF154BEEF907@microsoft.com... >I have inserted text into my newsletter (us...

The number of columns for each row in a table value constructor must be the same.?
I have the following script that inserts rows into the table type and sends the table to the DeleteHeadlines stored procedure. 1. The rows listed below that are inserted into the parameter already exist in the table itself. 2. The stored procedure uses a merge to delete all of the rows if they exist in the source/target. 3. I get the following error on line 11: "The number of columns for each row in a table value constructor must be the same." 4. In order, the tables columns are HeadlineID,HeadlineTitle,HeadlineDescription,HeadlineContent,HeadlineStartDate,H...