Cannot Convert Numbers Stored As Text To Number Format

Dec 14, 2009

There is probably a really easy answer to this question but I cannot seem to figure it out. I need to calculate the sum of pages faxed on a fax report at my office on an excel spreadsheet which is downloaded from our server. It appears the file is not meant to be used in excel but it downloads as an "*.xls" file anyway. When I open the file I receive this message:

"The file you are trying to open, 'FaxCetailReport.xls', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

The file is comnig from a trusted source so I select "Yes" and it opens just fine. There are just over 50 rows of info, one of them being the number of pages. In a blank cell I enter "=SUM(select rows)" and the result is a "0". I have converted the cells to the "number" format, no change. When I right click on the cells and select format, the protection tab on the right has the check box "Locked" checked and unchecking it does nothing, it is checked as soon as you go back in to the protection tab.

The only way around this is to manually retype the numbers in a cell next to the pages column, which is ok for 50 numbers, but I have some sheets where there is over 1,000 entries.

View 9 Replies


Convert Numbers Stored As Text To Numbers (attached File)

Oct 22, 2009

In the ID column of the attached excel file whenever I convert the numbers stored as text to numbers it results in a weird transformation.

e.g. an ID of 480610141001 becomes 4.8061E+11. When I do the same process in the name column, which has similar numbers, it will give the correct result following the same process.

View 2 Replies View Related

Macro To Convert Numbers Stored As Text To Numbers?

May 23, 2014

I have been looking through the forums and found the below code, but I have both text and numbers in the same range. I have attached an example of what I need to have converted.


View 3 Replies View Related

Convert Numbers Stored As Text To Text

Dec 2, 2009

I have the following macro that I use to convert numbers stored as text to text. For some reason, if I try to use it twice in a row, it won't work, I get an error that says "application-defined or object-defined error" and highlights this part of the Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1).Copy

View 5 Replies View Related

Convert Text Format To Numbers Format In Vb

Nov 21, 2006

how i convert text format into numbers format in vb.

i have a formula in vB:

Private Sub Textbox3_Change()
Textbox3 = Val(Textbox1.Value) + Val(Textbox2.Value)
End Sub

however..when i sum it up (in excel) using"=sum" ooes not sum up

I faced an error "number stored as text" to i convert it to numbers format in vb.

View 9 Replies View Related

Converting Times Stored In Date Format To Number Format?

Mar 31, 2014

One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.

View 5 Replies View Related

Excel 2010 :: Convert Text To Number And Format Number Without 2 Decimal Places?

Oct 23, 2011

I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.


View 4 Replies View Related

Convert Number Format To Text Format?

Oct 11, 2012

I want to convert number format to text format, any formula ?

View 6 Replies View Related

How To Convert TEXT Into NUMBER Format

May 5, 2007

I just copied a range of data from a website however these numbers are in TEXT format.

Basically each number in this data set has a SPACE behind. This turn the number into a text itself and i cannot do a sum for this range of data. I tried TRIM function and also tried to format it to number but no luck. Also tried to mutilply the range to 1 yet they're still in text format.

View 9 Replies View Related

Converting Numbers Stored As Text To Numbers Via Macro

Feb 9, 2007

I'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.

View 7 Replies View Related

SUMIFS And Numbers Stored As Text

May 5, 2014

I do have the following problem:

When I use the SUMIF(S) function and a criteria range that has numbers stored as text (eg: '394) the unequal operator (<>) doesn't work.

=SUMIFS(B:B,C:C,"<>493") doesn't work

I'm able to get the salary of employee 493 by using "493" instead of "<>493", but it seems that with the unequal operator Excel starts treating this ID as number. The only work-around I found so far is to use a wildcard e.g. "<>*493" or "<>493*". Excel then treats it as text again, but this is dirty workaround. What happens if an employee with an ID e.g. 1493 shows up!?

"<>'493" or something doesn't work unfortuantely..

Employee ID




View 14 Replies View Related

Sort Numbers Stored As Text Not Working?

Sep 17, 2013

In a spreadsheet I have a text column with entries such as:
10 Times
10.5 Times
101 Times
25 Times
A Pure Text Entry

When I sort by this column I expect to get:
10 Times
10.5 Times
101 Times
25 Times
A Pure Text Entry

Instead I get the 1st order. Why? I've selected 'Sort numbers stored as text' separately so it shouldn't be treating 2012 as a number. Besides I've checked & the cell formatting is Text.

View 3 Replies View Related

Automatically Converting Numbers Stored As Text To Numers

Oct 28, 2008

I am using an ODBC database connection to input data into an excel file. When I input the data excel stores the numeric data as text and puts a ' in front of the number. There is an exclamation mark beside the cell which allows me to convert the data back to numeric format. I need excel to do this conversion for me automatically when new data is added to the file.

View 9 Replies View Related

Converting Text To Numbers To Useable Number Format

Sep 29, 2008

Is there anyway of converting the output (which is 7 and in text form) to a useable number format so that I can take advantage of the VLOOKUP function (instead of getting a #N/A)....

View 9 Replies View Related

Converting Number Stored As Text

Mar 12, 2012

I have a spreadsheet which has over 30,000 records. One column is a row of numbers stored in a General format. Some of these numbers are however stored as Text and have the following Error Check:

"The number in this cell is stored as Text or preceded by an apostrophe"

As a result, when I try to import this sheet into Access, the numbers are not imported and appear as blank cells in the Table.

Is there a way either manually or programatically, format the column so all cells in that column are stored as a number and do not have the issue of being stored as text?

View 4 Replies View Related

Custom Format That Includes Text And Numbers, Such As Medicare Number

Jan 19, 2006

how can you custom a cell format in which you may in type in information that is displayed as ###-##-####-@# I searched everywhere and cannot find this.

For example, a medicare number.

I would need to type in 581566223b6, and it should be displayed as 581-56-2236-B6.

View 9 Replies View Related

Number Stored As Text - Make Column As Numeric Permanently

Nov 29, 2011

I have sent a data sheet template to my client in which they paste their data, based on this data sheet analysis will be done automatically in next sheet "Summary".

Whenever they paste product code, vlookup is not working since the product code contains few are numeric (shows: "Number Stored as Text"). I advised my client to go with "Text to Column" and "Delimited" to change it as numeric. But my client feels this method is tedious one. Is there any alternate way to make that column as numeric permanently?

View 5 Replies View Related

Convert Number Format To Time Format

Nov 28, 2013

How do I convert 11.20.00 in A1 to 11:20:00 in B1?

I've tried =TEXT(A1,"hh:mm:ss") to no avail.

View 3 Replies View Related

Convert A Date Format To A Text Format

Oct 6, 2008


In D2 I have: =B2
In D3 I have manually input: (space)3-08

As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!

View 3 Replies View Related

How To Convert Big Hexadecimal Numbers To Decimal Format

Mar 19, 2013

I need to be able to convert big hex numbers into decimal numbers. The problem that I have with this is that when you put a hex number that includes letters excel considers it text. And if the number is too big, you can't convert it to decimal format, and when you try to, it comes up with the #NUM! error.

Number: "78652385098375A879FF747C9847A00" without quotes

View 4 Replies View Related

Convert Downloaded Web Page Numbers Seen As Text To Numbers

Jan 16, 2008

See attached file. A colleague is downloading rows of data from a website which contains a number field Excel is currently treating as Text after being pasted in. My spreadsheet includes just a sample of the many rows of data however as you can see the VALUE function refuses to convert these text values to numbers. How these might be converted and why the VALUE function refuses to work in this case?

View 5 Replies View Related

Convert Decimal Numbers (Minutes) To Time Format

Mar 8, 2013

How to convert decimal numbers which represent minutes into time format? Like this

Sheet3  GH1MinutesTime212.9729729700:13:00325.9459459500:26:004116.756756801:57:00

View 2 Replies View Related

Remove The "Number Stored As Text" Error

Jul 18, 2006

I am using a macro do display actual date, by copy pasting special, so that the day and month is always 2 digits: ie. 01, 02, etc. in case less than 10.

Private Sub Workbook_Open()
Sheets("Sheet2"). Range("C4").Select
ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""dd"")"
ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""mm"")"
ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""yyyy"")"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sheet2").Range("C4:D4").NumberFormat = "00"
End Sub

I paste special so the user does not see this formula. However, there is that annoying notification saying it is a number stored as text. What is the universal way to switch this warning off (or to better write the macro) so this warning will not be on any version of Excel?

View 4 Replies View Related

Convert Numbers, Seen As Text, To True Numbers

Aug 26, 2006

The back story is that from a system we use, when you download data from inside it, Excel treats most of the cells with a "Number Stored As Text" error. I had problems with this and it was just generally annoying so I wrote the below macro that converts each of the cells back to general cells and reinputs the data, essentially reseting the cell and keeping the data. Being that this is a loop, it takes a good while to run if alot of data was downloaded. Any idea's on how to speed it up besides screen updating? Note, other stuff happens in the first macro, but it is just cosmetic non-intensive things. It calls the second macro because I wanted them seperate should I want to just run the second.

Sub CleanData()
Application. ScreenUpdating = False
' Find the Right most cell and the Bottom most cell and
' run the macro from A1 to this cell recursively
Col = Selection.Column
Row = Selection.Row
Range("A1", Cells(Row, Col)).Select
Application.Run ("FixTextAsNumberError")
Application.ScreenUpdating = True
End Sub..............

View 5 Replies View Related

Convert Numbers As Text To True Numbers

Sep 29, 2007

I have the following two macros that I use daily:

Public Sub add_tick_marks()
Dim c As Range
For Each c In Selection
If Not IsError(c.Value) Then
If Left(c.Value, 1) <> "'" And c.Value <> "" Then
If InStr(c.Value, "E+") > 0 Then
c.Value = "'" & c.Formula

I use these because I frequently copy billing account numbers that have leading zeros from some proprietary software and work with them in Excel. I need the leading zeros to facilitate VLookUp's. When I paste the data into Excel it truncates the leading zeros. My typical procedure is:

1. Copy a column of values from our proprietary software
2. Paste it in Excel (Ctrl+V)
3. While newly pasted data is still selected I hit Ctrl+1 which brings up the Format Cells form.
4. I double click "Text"
5. I re-paste into Excel (Ctrl+V)
6. I run my macro to add tic marks to the values so the leading zeros never get lost.

The problem occurs when I copy multiple columns of data and paste it in Excel. If one of the columns of data contains numbers or currency then I select that range of values and run the macro to get rid of the tic marks. After running the macro the numbers are still treated as text. When I select a range of those values and right-click on the status bar and select " Sum" there is no value. No big deal, I just highlight them and format the cells as numbers or currency. But after formatting them as numbers or currency, they retain their format as text until I click in the formula bar for each individual cell and hit enter, at which time they take on the number or currency format I have chosen and the sum can then be displayed in the status bar.

How can I force the formatting I chose to take effect on the data immediately without me having to individually "fake change" the data in each cell?

View 3 Replies View Related

Convert Numbers Seen As Text To True Numbers

Nov 21, 2007

I have text as numbers downloaded to excel sheets from crystal that i need to add together on a summary sheet. I've tried using Value, TRIM and CLEAN functions to no avail. the TYPE function is reading the cell as Text and I have also tried multiplying by 1. Does anybody know how I can get around this ? hopefully I've attached an extract of the file

View 4 Replies View Related

Convert Number To Date Format

Nov 14, 2011

I have following in cell A1


This represents 22-Dec -2011. Formula to convert the number in to a date format.

View 3 Replies View Related

Convert Number To Percent Format

Dec 18, 2008

I have built a spreadsheet that pulls data into B60:AA240 (Sheet name is "Actual Numbers Report") from a different sheet in the same workbook. Some of the data is in Number format and the other is in Percent Format. What I would like to do is if AL10 in the Actual Numbers Report sheet says "Actual Numbers" then I would like the cells in B60:AA240 convert to a number format "000,000,000" If AL10 says "Trends" then I want it to convert the cells in B60:AA240 to a percent format "0.0%". I tried creating some code, but it doesn't seem to work.

Private Sub Convert_Percent()
If Not Intersect(Target, Range("B60:AA240")) Is Nothing Then
If .Range("AL9") = "Actual Numbers" Then
Selection.NumberFormat = "000,000,000"
ElseIf .Range("AL9") = "Trends" Then
Selection.NumberFormat = "0.0%"
End If
End If

End Sub

If this can work then the 2nd question I would have is can this same line of thinking work to format the chart that this data is pulled from? So if it is Actual Numbers the chart would be in a number format and if it is Trends then it will change to a percent format?

View 9 Replies View Related

Convert Number To Date Time Format?

Dec 9, 2013

how to convert number to data time format:

eg. I have number 20121231230000 and I need its conversion to 31/12/2012 23:00.

I have tried to use fucntion date with left, mid, right but still I do not know how to extract time.

View 2 Replies View Related

Convert Serial Number To Date Format

Nov 27, 2007

Quick question:

If I have a serial date, e.g., 37883, in Excel, how can I convert to 092007, using that exact format?

<snip by admin. Read the rules!>

View 4 Replies View Related

Copyrights 2005-15, All rights reserved