Convert text to number - whole column

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

Hello, <br>
I have an excel file of > 5000 records, sent to me by a windows user, and the values are not recognized as such but as text (so filtering and formulas don't work).  I can only convert 1 cell at a time, so selecting the entire column and then changing to number does not work unfortunately... <br>
I hope I can sort this out, thank you so much in advance for helping ! <br>
Liselotte, Belgium
0
Lise
1/30/2010 4:00:19 PM
mac.office.excel 1146 articles. 0 followers. Follow

4 Replies
8300 Views

Similar Articles

[PageSpeed] 29

Are the cells formatted as text,  or do they have a " ' " in front of 
the numbers?  If the latter,   doing a global Find&Replace will solve 
your problem.

If the former,  what happens when you select an entire column and choose 
a Number format from the menu?

In article <59bb1e3c.-1@webcrossing.JaKIaxP2ac0>, Lise@officeformac.com 
wrote:

> Version: 2008
> Operating System: Mac OS X 10.5 (Leopard)
> 
> Hello, <br>
> I have an excel file of > 5000 records, sent to me by a windows user, and the 
> values are not recognized as such but as text (so filtering and formulas 
> don't work).  I can only convert 1 cell at a time, so selecting the entire 
> column and then changing to number does not work unfortunately... <br>
> I hope I can sort this out, thank you so much in advance for helping ! <br>
> Liselotte, Belgium

-- 
Team EM to the rescue!    http://www.team-em.com
0
Carl
1/30/2010 7:27:22 PM
Lise@officeformac.com wrote:
> Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Hello,
> I have an excel file of > 5000 records, sent to me by a windows user,
> and the values are not recognized as such but as text (so filtering and
> formulas don't work). I can only convert 1 cell at a time, so selecting
> the entire column and then changing to number does not work
> unfortunately...
> I hope I can sort this out, thank you so much in advance for helping !
> Liselotte, Belgium

One way to fix this problem is to copy the cells that are formatted as 
text and paste them elsewhere using Paste Special > Values.

-Jim

-- 
Jim Gordon
Mac MVP
Co-author of Office 2008 for Mac All-in-One For Dummies
http://tinyurl.com/Office-2008-for-Dummies
0
Jim
1/30/2010 8:44:52 PM
Thank you jim, I have tried so, but unfortunately, it stays formatted as text.  I have tried all paste special possibilities...
0
Lise
1/30/2010 9:59:15 PM
Hi Lise;

You might try entering the digit 1 into a cell, copy it, then select the
column of figures. Use Edit> Paste Special - Multiply [in the Operations
group], OK.

If that still doesn't get the job done there must be something else about
the content of the cells that you aren't recognizing � spaces, non-digit
characters � that are preventing that content from being recognized as
values. In Excel 2008, even when the cell is formatted as Text or the
apostrophe (') precedes the digits the content can still be used in
calculations as long as the characters in the cell can be parsed as a value.

HTH |:>) 
Bob Jones 
[MVP] Office:Mac



On 1/30/10 4:59 PM, in article 59bb1e3c.1@webcrossing.JaKIaxP2ac0,
"Lise@officeformac.com" <Lise@officeformac.com> wrote:

> Thank you jim, I have tried so, but unfortunately, it stays formatted as text.
> I have tried all paste special possibilities...

0
CyberTaz
1/31/2010 3:14:23 PM
Reply:

Similar Artilces:

formula tu sum up to a number till 7
This is the data I have: 2 3 4 5 5 6 6 7 7 1 4 5 1 2 I want in column C to G cells to be filled till 7 starting to count from B and then starting again. Result I want: A B C D E F G 2 3 4 5 6 7 1 4 5 5 6 6 7 7 1 4 5 1 2 And so on... How can I do this... Thank you so much!!! I don't fully understand this. Why do you not show 2 as the starting value in column C? Why do you have a 1 after the 7? Is this the corresponding value from column B? Would C2 start with 4 and continue across until you next...

Change default currency of Opportunity when convert Campaign Respo
Whenever we convert the campaign response to Opportunity, the currency on the Opportunity is always base currency and it cannot be changed. Is there anyway to make this auto created opportunity has another currency?? The transaction currency in Opportunity is drived from the original campaign from which you created campaign response. If you want your Opportunity has other currency, you need to set it when you create your campaign from the very beginning. "dorekofu" wrote: > Whenever we convert the campaign response to Opportunity, the currency on the > Opportunity is a...

How can I asign a number value to a text line in Excel?
tI have a Backgammon Club with Internal Club Rankings that are in Text. I want my members to report their wins - 1st, 2nd, and 3rd place - in tournaments to my web site . I would like them to imput: their nickname, date of tournament, tournament Room #, tournament points played for, and host of the tournament. I would further like the calculation to display any change in Ranking as a text cell. Additionally, I want to show for the member, how many more points are needed to get to the next levle and what that (Text)level is. If there is a "what if" for this please advise. Ri...

Formula argument based on text
Hi All, I have cells (A1:A10) populated with text ("Pass" or "Fail"). In cell (A11) I want to have a formula that returns the text: "pass" if all the cells (A1:A10) contain the text "pass" "fail" if any one of the cells (A1:A10) contains the text "fail". How would I write the formula to achieve this? Regards gregork In A11 enter: =IF(COUNTIF(A1:A10,"Fail")>0,"Fail","Pass") HTH "gregork" <gregork@paradise.net.nz> wrote in message news:0RXSb.19911$ws.2664893@news02.tsnz.net... > ...

new emails
When I start a new email the text looks very small and hard to read but when I look at it in my sent folder it looks fine.... why? Have you checked your zoom level already? See http://www.msoutlook.info/question/95 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "Mike Bunting" <Mike Bunting@discussions.microsoft.com> wrote in message news:228BBE80-21D2-4ADF-9048-CFB6C9DA3E9...

Control can't be edited; it's bound to a replication system column 'TableName'.
I have an Access 2007 accdb database. It hast a table in it with field names TableName and FieldName and a bunch of others. This table has had a small number of records added to it (37) over the life of the database. Suddenly, the two fields TableName and FieldName cannot be edited. That is even if the table is open as a table and you try to type a new record. When you do that an error message is briefly displayed in the left hand end of the Access main window's status bar. For example if you try to input anything into the TableName field you will be blocked and you will briefl...

Convert XmlDocument to XpathDocument
Hi, I've an object of XmlDocument, I need to pass it to some function which takes XpathDocument object, How could I convert? There is not Conversion Operator overload like that. Your reply might help me a lot. Thanks, Fahad Fahad Ashfaque wrote: > I've an object of XmlDocument, I need to pass it to some function which > takes XpathDocument object, How could I convert? There is not Conversion > Operator overload like that. You can save to a MemoryStream and then load from that stream e.g. MemoryStream memStream = new MemoryStream(); xmlDocument.Save(memStream)...

Preventing Column Cell Values from Printing
I have a summary worksheet with several columns of information. There is one particular column (E25:F39) where I don't always want to print the values. Is there some simple way to toggle this column's values on and off just before hitting the print command? I was thinking of using an adjacent tick box outside the print area, which when ticked would print the column values, and vice-versa. I'm looking for a formula that would examine the tick box, and if the tick (letter 'a' in Marlett font) is not present (i.e. cell value = ""), then the values in column (E25:...

How do I force all capital in a column of lower case names?
I read the help, but it doesn't tell me where to enter the formula. If you do not have a blank column next to the mentioned column, then insert one. Enter the formula in a cell adjacent to the cell you want to change, and copy down. Then copy this column, go to the original column, select a cel, right click and click on Paste Special, tick values. Delete the helper column. Iow, with you lower case data in Column E, click on Column F, if not empty, and insert a column. F is then an empty column. Say your data starts in E2, then in F2 enter =UPPER(E2). Move cursor to the botto...

How to find a value with multi-column, multi-record list
I have a spreadsheet with $costs arranged according to values in both rows and columns. How do I create drop down lists for both the rows and columns and then return the cell value? eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each cell within this group how do I create drop downs for each selection, 2-6 and B-G, and return the value to a selected cell. In a typical table of this nature there would be descriptive row and column headers. To do a lookup on this table you would then look for the intersection of a specific row header and a specific column ...

option to show or hide social security number throughout system
Now that identify theft is a big issue, a great feature in HR and/or UPR would be an option to hide or show SS# on windows and reports. I'm having to cusomize HR and Payroll reports just to remove the SS#. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://ww...

Column Width Behavior
I use pivot tables alot - and I am working with a pivot table where I've set the column width to 9. When I pivot new information in, the column width automatically adjusts to fit, then I have to go and reset the columns back to 9. Is there anyway to override this "adust to fit" behavior and just let the pivot table fit as much text as it can into the column width I specify? Kirk, Unselect the 'Autoformat Table' option in the Options section of the Pivot Table. HTH, Gary Brown "Kirk P." wrote: > I use pivot tables alot - and I am working with a pi...

Check Number format
In Great Plains our checks were initially set up with 20 zeros. For some reason when running the Vendor check request report on some of the PC’s (on screen and to the printer) the entire check number does not show up so it was requested that we remove 8 of the leading zeros. I went into Cards>>Financial>>Checkbook and pulled up the appropriate checkbook and removed the leading zeros from Next Check Number and Next Deposit. When I print the report, the zeros are still visible. Am I changing the correct field or will this change not affect existing numbers in the report I...

Move gridlines in a chart so that they are above the numbers
I have done a chart in excel and the gridlines are set so that the y axis numbers are inbetween the gridlines. When I choose minor gridlines I get double the gridlines. I just want a gridline above each number in my y axis Double click the axis that intersects the gridlines, and on the Scale tab, uncheck Axis Crosses Between Categories. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tara Medway" <Tara Medway@discussions.microsoft.com> wrote in message news:47400C54-74B4-4E67-93AD-AE8B3F09BE47@microsoft.com...

"Changed by" Column is empty
Outlook 2003/Exchange 2000 Hi, in my mailbox, the "Changed by" column remains empty throughout all folders. When I change to my Team Mailbox, the "Changed by" column has the desired information. Even when I move a mail from the Team Mailbox to my mailbox, the "Changed by" column remains empty. Is this a bug or I am doing something wrong. Thank you very much. Christoph ...

shortcut ket to change formatting of text
hey, whats the shortcut key to change numbers to dates, and vice versa. cheers : -- Message posted from http://www.ExcelForum.com Hi! This may be the sort of thing you want: CTRL+SHIFT+# Apply the Date format with the day, month, and year. This is a quote from Excel Help ( use "Keyboard shortcuts" for th search) Al -- Message posted from http://www.ExcelForum.com ctrl-1 will pop up the Format|cells dialog ctrl-~ (ctrl-tilde, aka ctrl-shift-backquote on my USA Keyboard) will format a cell as General ctrl-# (ctrl-shift-4) will format as d-mmm-yy Try searching excel's...

Auto Number
Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields. In the bound form, frmProvider, I would like txtProcessProviderNumber to increment by 1 on each new record each new record entered during the Year. The year will be entered manually by the user via combo box in text field. Once the Process year changes to a new year I would want the numbers to start all over at 1. How can I do this. Any help would be greatly appreciated. -- Gary i n Michigan, USA GaryS wrote: >Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields. In >the bound form,...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

converting plain data to table format
It has been a while since I used excel but I am almost sure there is a way to convert just plain rows and columns of data into a table format. This way I believe that adding rows to this block of data is as simple as tabbing after the last cell and it should just drop down to the next row and insert one??? correct?? Excel should recognize a contiguous range as a table, or list. Include headings in the first row, and leave at least one blank row and column between the table and other items on the worksheet. You can use the built-in data form to add records to the table (Data>Form). Or,...

sum numbers and ignore text
Hi there, I need some help please. I have a row of numbers and text and I need to sum the numbers only. How to do? a1 a2 a3 a4 a5 1 dnr 3 4 dnr total 8 thanks in anticipation ditchy Ditchy, a simple SUM formula will do it, since the SUM worksheet function ignores text values. So in your example, it's =SUM(A1:A5). -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "ditchy" <ditchy@ncable.net.au> wrote in message news:dda1ad29.0405221934.5787f60f@posting.google.com... > Hi there, I need some help please. I have a row...

Line Numbering
Can I add line numbers to an excel spreadsheet? If so, how? Thanks "Ernie" <edeleon68@hotmail.com> wrote in message news:126f501c3f6ef$e59575e0$a301280a@phx.gbl... > Can I add line numbers to an excel spreadsheet? If so, > how? > Thanks Use the formula =ROW() copied down. Hi do you neet the row numbers on your printout? If yes goto 'File - Pagesetup - Table' and check the option to inlcude row and column headings -- Regards Frank Kabel Frankfurt, Germany Ernie wrote: > Can I add line numbers to an excel spreadsheet? If so, > how? > Thanks ...

Typing text is impossibly slow in text boxes when in publishing layout
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I try to type or edit text in a text box in a template document (a newsletter template) the typing, editing, deleting, backspacing or what ever, is absolutely impossibly slow. Am I doing something wrong or do I have something set up wrong. If it remains this way I will not be able to work in this program. Apart from using Publishing Layout View, I suspect you are not doing anything wrong. Publishing Layout View requires very powerful hardware, and even then it tends to crash a lot. If I were you...

selecting text in a document
Hi All, I have a macro that seeks out a particular string of text in a document and then moves down one line and selects the remaining text nin the document and writes it to a variable and then uses it subsequently in the message section of an email. Recently this method has been failing because the text is not being selected. I don't know if the PC was in EST mode a the time but I need the macro to work no matter what. The code I use is as follows Selection.HomeKey wdStory Selection.Find.Execute "Issued at" Selection.MoveDown wdLine...

text import wizard #4
I'm migrating a huge database out of our current business software int new software. I'm exporting into an Excel file so I can adjust inf before mapping to the new software. I've got the import wizard figured out but I'd like to *use it within column* of info and not just to import a whole file. are the functionalities of the wizard available within a column -- Message posted from http://www.ExcelForum.com Many of the capabilities are available if you choose the Data/Text to Columns... command what exactly are you looking for? In article <philtyler.1a7i9n@excelforum...

How do I use alpha page numbers (e.g., B-3a, B-3b, B-3c, etc.) in.
I am using Excel 2003 and need to use incrementing alpha page numbers (e.g., B-3a, B-3b, B-3c, etc.). Excel 2003 help only refers to numeric page numbers. Is there a way to format the page numbers to use incrementing alpha page numbers? I was hoping to see a better reply to your question, but a day has gone by so here is my $0.02. If you must, you can always similate the footer in the worksheet, just have to be consistent with row heights, etc. This might be more trouble than it's worth, but if want to try here's a formula that might help. =IF(QUOTIENT(COLUMN()-1,26)=0,"...