Number Stored as Text
When importing data from external sources you may find that your number values unexpectedly import as text.
It’s usually obvious when this happens - the numbers are left-aligned.
The cells may look like a number, but Excel thinks that they are text.
You’ll find that you can’t perform calculations against “text-numbers”. For the picture below, if I used the formula =SUM(A1:A10) then the result would be 0.
Excel 2002 (Excel XP) made some advances in this area by way of automatic error-checking (aka the Green Triangle).
You can quickly convert the cell to a proper number by highlighting your list of misbehaving numbers, click the exclaimation mark and choose ‘Convert to Number’ from the dropdown.
For those of you running a version of Excel less than 2002, the trick I use goes as follows:
1. Copy a Blank Cell (or a cell containing the number 0)
2. Select your list of text-numbers
3. Choose Paste Special from the Edit menu.
4. Paste=Values, Operation=Add
By applying a math operation on the text-numbers, the result is a number!
Sure beats pressing “F2 Enter” 100 times like I used to.
Charlie III:
Nice little “How Too.” I come across this issue many times with my coworkers and I show them the work arounds. I use the green triangle method for small columns. It shure beats F2 enter. But, I found for longer columns, 5000 rows or more which is not uncommon for me, that have both types of data, text and text-number, a small quick function works faster. Once the function calculates the answer, I copy and paste value over the old data. Then I delete the column with the function. So far I haven’t had problems. The function is as follows:
=IF(ISERROR(VALUE(A1),A1,VALUE(A1))
I should test this method and time it compared to the yellow caution sign.
18 February 2006, 7:18 amJon Peltier:
Charlie -
Try the Copy blank - Paste Special Add technique that Rob suggests. It’s much quicker than your technique, as it doesn’t require the use and subsequent deletion of a column of formulas.
18 February 2006, 9:09 amRobertV:
If the text data in the worksheet cel (e.g. US domestic 1,000.00) is not represented in your local worksheet data format (e.g. Continental 1.000,00), the technique will not work. This frequently happens if you import foreign data. It gets even more messy if you have to work with dates.
19 February 2006, 3:28 amLori:
To convert the type of data in a column it’s quicker and more general to use Text to Columns from the data menu.
- For numbers or formulas formatted as text simply press finish to change to values
(assuming tab-delimited is default)
- To convert numbers or formulas to text choose the text option in the 3rd step of the wizard.
- This also has options for converting dates and number formats independent of the locale.
For multiple columns in large ranges, copying to office clip board (by pressing ctrl+C twice) and pasting back also has the effect of evaluating the data. In Excel 2002, you can choose the text import wizard option on the smart tag to choose the data format of columns.
19 February 2006, 4:11 pmRob van Gelder:
Great Tip - thanks Lori
19 February 2006, 7:58 pmLori:
np - happy to share it :-). It’s particularly useful for converting data before exporting to databases. For example dates entered as “February 20, 2006″ or “20060220″ can be converted to Excel dates by choosing the required date format (MDY or YMD).
The office clipboard method has a number of uses too.
It needs at least around a page of data (2k) so that it gets copied in as text. eg:
- Convert a whole sheet of data to text by copying then formatting the number as text before pasting back.
20 February 2006, 6:26 am- Choose the import text wizard with tab delimiter to format multiple columns or without delimiter to concatenate all data into a single column.
- Or to evaluate formulas referring to external sheets such as =”=vlookup(a:a,”&b:b&”,2,0)”.
where column a contains lookup values and column b contains the list of references (sheet1!a:b,sheet2!a:b,..).
Charlie III:
Rob- I had to test the speed between types and yes your method is faster. I tried on a list of 1134 items. The items were mixed between numeric-text and text. I used the sames spreadsheet for 3 tests. Your method above took 25 secs. My equation took 55 Secs. But When I used the Excel Green Trinagle and converted the whole row, it took over 5 minutes! Thanks.
20 February 2006, 6:45 amGareth Forster:
I think I`m on the same track as the underlying cause of the Sort problem is the same - my notes advise the following:
Sorting a column of numbers doesn`t work correctly:
If a range of cells containing numbers is sorting incorrectly, apply a numeric format to the cells. Enter “1″ in a blank cell and ‘copy’ it. Highlight the cells which are sorting incorrectly. Paste Special, under Operation, click Multiply.
I got this from the Excel 2000 help
I may be missing a subtlety of the problem being addressed though?
23 February 2006, 4:12 amMatt H:
I must be lazier than most. This comes up for me so often, that I wrote this macro, and assigned it to a custom button. One click!
Sub ConvertToDouble()
‘Converts numbers stored as text to type Double
Dim cell As Range
For Each cell In Selection
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
End If
Next cell
End Sub
1 March 2006, 1:51 pmGordon Bell:
If you know the range you wish to convert, you can accomplish this easily in VBA using:
Range(”A1:C100″).Value = Range(”A1:C100″).Value
Matt, in your macro, you can actually use this much more efficient method:
Selection.Value = Selection.Value
24 March 2006, 1:16 pmMatt M:
Thank you Rob, and Charlie III. You guys saved me the major headache of manually handling a load of data.
3 July 2006, 2:21 pmKate:
This is kind of a sloppy way to do it:
1. Save As Tab Delimited Text
2. Close File
3. Open File again, selecting the Tab delimiter in the dialog
4. All tick marks will be gone, although it may add some
quotes to some fields, these can easily be removed with Find/Replace
Kate
16 August 2006, 1:01 pmn8 Mills:
Sweet! Good work you guys.
Of course Microsoft doesn’t address the problem directly on their website, maybe because they don’t want to publish work-arounds.
19 August 2006, 2:50 pmCAM - Blog » Blog Archive » Excel - ‘Number Stored as Text’:
[…] Unfortunately simply changing the number format or alignment of the cells involved does not solve the problem. I like the information in the links below - they cover most of the helpful answers/suggestions that I have seen: Daily Dose of Excel - Number Stored as Text (the post AND comments are good reading) Jim’s Help Pages - Problems with imported data (also very good reading: Excel KB articles) […]
23 August 2006, 1:00 amMalcolm:
I need the opposite of what you have been discussing. I need a text element, such as 0512337689 (a telephone number) to remain as text when the file is saved as a tab delimited file. My experience is that the phone number is converted to a number, dropping the 0, which then gives a bad phone number. I can click on Ignore error, but I don’t want to do that thousands of times…..
Help will be appreciated.
4 September 2006, 9:53 amAlan:
Ditto what Malcolm says -
I frequently import data from databases, and invariably have a bunch of the green triangles on data that should not be treated as numeric, but happens to consist of only numbers in some cases (serial numbers, part numbers, etc).
I can turn off error checking for Number Stored as Text in the Excel options, but that only fixes the irritation for me. The people I’m sending data to (always different people) are presented with spreadsheets that appear to be full of error warnings, very unprofessional in appearance.
Drives me mad, since there is no problem with the data as-is. Any ideas how to tell Excel that this is meant to be text data? I tried prepending an apostrophe, no luck.
15 September 2006, 7:30 amJunnick:
Yo! guys! im maybe not in the right zone. but i just want to say aloud my problem
in formatting a cell in a text format using vb.net code. Actually im exporting the data
from a datatable to an excel file.
Heres what my code look like:
21 September 2006, 1:38 am‘Create a new workbook and save to mapped network drive
‘Open the newly created file and do some loops from the datatables
‘and format the cell and reflect its value
.Worksheets(1).Cells(row, column).NumberFormat = “000###”
.Worksheets(1).Cells(row, column) = datatable.rows(0).item(1) ‘Value is 000123′
‘actually the output is correct but
‘the problems is the cells format is not actually what i expected. The format was change to ‘Custom’ which is correct. Id just the cell to change its format to ‘Text’ which i know its possible but i really dont know how to format cell to text programmatically. Any help mail at
ayso@cebucity.gov.ph
veh:
You should get the Nobel Prize or something for this–I spent at least an hour trying to figure out why my formulas didn’t import values!
22 September 2006, 12:45 pmRob van Gelder:
Malcolm and Alan:
- Select your range of cells
- Right click the selection, click Format Cells…
- Using the Number tab, click Text from the list
It forces Excel to treat numbers as text.
Cheers,
23 September 2006, 1:32 pmRob
Alan:
Rob:
Since the number is stored as text, the number formatting doesn’t have any effect on it.
I suppose I could convert them all to numbers and then use the text formatting option as you describe, but it seems like a long way around when I just want the text I’ve entered to be treated as text. It is as if Excel can’t believe that I would want to store numeric characters that were not to be treated as numbers.
26 September 2006, 2:57 pmRob van Gelder:
Alan:
The trick is to format the cells as Text before entering the numbers/text.
Rob
26 September 2006, 5:30 pmAlan:
Rob, you’re just messin’ with me right?
Blank worksheet, format a cell as Text, enter ‘123, and get the “Number stored as text” error.
Blank worksheet, format a cell as Text, enter 123, and still get the “Number stored as text” error.
Thanks for trying, I don’t really think there is a solution to this.
4 October 2006, 3:13 pmRob van Gelder:
Alan: you’re right, my mistake.
4 October 2006, 3:45 pmYou’re likely correct on this.
Lori:
I don’t think there’s an easy way to get round the error checking but to avoid the green triangles you could enter text values as formulas enclosed in quotes: =”0123″. This should convert all text values to formulas in a worksheet:
For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
5 October 2006, 4:45 amIf Cell.Value = Val(Cell) Then
Cell.NumberFormat = “General”
Cell.Value = “=”"” & Cell & “”"”
End If
Next Cell
Jon Peltier:
Lori -
Be consistent with your Cell.Values:
For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
5 October 2006, 5:21 amIf Cell.Value = Val(Cell.Value) Then
Cell.NumberFormat = “General”
Cell.Value = “=””” & Cell.Value & “”””
End If
Next Cell
Lori:
Yes it’s clearer and to allow for more digits replace the second line by:
5 October 2006, 8:00 amIf IsNumeric(Cell.Value) Then
IBN:
a bunch of the green triangles !!!
the quotes way works. but it changes cell value anyway.
the only way excel doesn’t show green triangle when “number saved as text”:
import data from a text file and select TEXT format!
if saved as XML file, the following is found added:
importtext
Text
936
.
,
Text
2
2
if this deleted, the green triangles appear again
or , if range name (”importtext”) deleted , the green triangles appear too.
NOT easy
16 October 2006, 9:51 amlisa:
I’ve been struggling with this one for a long time and I found the following method which best suits me:
Select the range where you get the errors.
Right Click on the exclamation point error icon.
Choose “Convert to number”
Voila! Done.
19 October 2006, 12:07 pmkevotheclone:
lisa it’s not that easy.
Sometimes we’ve got a digits with leading zeros (00001234) and we need to retain the leading zeros, Excel’s “Convert to Number” will delete the leading zeros.
Sometimes we’ve got digits that look like a date (9/99) but it’s really a numeric input edit mask, Excel’s “Convert XX to 19XX” and “Convert XX to 20XX” will reformat the data into a date.
Try it:
Format some cells as Text and enter 00001234 andd 9/99 and watch the “green triangles” appear.
Change the formatting via the “exclamation point error icon” and watch your original data get mangled.
And nobody want to click ion the “exclamation point error icon” hundreds or maybe thousands of times for imported data.
I’m a VBA guy and I’ve looked at the Range object’s Properties and Methods and can’t find any way to programmatically “ignore error”. I think Microsoft dropped the ball on this one, maybe it’ll be corrected in Office 2007.
7 November 2006, 6:00 pmGary Bouwman:
These tips and tricks do not work when the number format is 1.001,23 (for example).
This was a very intersting discussion thread. Everything described works as promissed until you through ‘global’ numeric formats at it. I’m developing applications for use globally. Regardless of location, most employees have their PC set to English, but I also need to code for that 30% that keep the PC in their language of choice. Dumping data base results to Excel in support of languages has taken me on a point where I’ve really learned more than I want on this topic and still only scratched the surface.
15 November 2006, 12:51 pmGary Bouwman:
I found a work around that seems to work for me. Not as simple as some solutions, but as I mentioned in my previous note, those simple solutions did not work for me. This solution uses a text-to-column converter which I’ve written into my code.
http://cmiles.wordpress.com/2006/08/23/excel-number-stored-as-text/
Gary
15 November 2006, 2:51 pmLori:
Gary - Can you not use Data > Text to Columns > Next > Next > Advanced… for converting different number formats? I think this setting may only appear in later versions of Excel.
16 November 2006, 11:32 amNico:
Hi All.
Here is the answer you have been looking for.
First create the WorkBook object.
“Dim wb as Microsoft.Office.Interop.Excel.Workbook”
If you have a different library(dll), use that to create the workbook object of excel.
Then create the Style object.
“Dim style as Microsoft.Office.Interop.Excel.Style”
Again, if you have a differnt library(dll), use that. For me, i used
“Dim Style as Excel.Style”
Then add a Style to your workbook.
“Style = wb.Styles.Add(”Style1″)”
Set the Style formatting as text.
” Style.NumberFormat = “@” ”
And then use either the cells.style = “Style1″ or the range.Style = “Style1″.
This will set the numberformat of that cells, or that Range to “Text”.
Thus, if you have “000323″, it will be shown in the excel sheet as “000323″ and not “323″.
If you dont understand what i said, then you can go and read the forum, where i got this from at
http://www.thescripts.com/forum/thread385790.html
Enjoy. I got my excel to work correctly after hours of sitting on the numberformatting problem.
21 November 2006, 12:53 pmGary Bouwman:
Nico,
I understand the concept as I’ve used styles in web development, but I cannot get this example to work with VB6. Is .NET required?
Yes I came back looking for another solution. My fix worked for many locales, however it has problems with French settings - other European countries tested appears OK. Those little green triangles are getting the better of me.
Lori,
The text to column solution is what I was using. When the user has a PC in French locale settings this solution works for any numbers greater than 1, however numbers less than 1 remain using a decimal point (i.e. 0.123 instead of 0,123). In a German, Dutch, and Spanish locale, the solution appears to work all the time.
Regarding another solution discussed,
22 February 2007, 11:55 amThe copy (cell containing 1) and pastespecial solution (multiple) works interactively, but does not convert text to numbers when run via VB.
Brent:
Hi,
I see lots of solutions that work. But I don’t see anyone saying why this happen in the first place.
I export data from a db via asp. I have not change the code or data in years. but all of a sudden I’m getting numbers stored as text. I have tried in the asp FormatNumber() and formatcurrency() and trim() and lots others but some of my data still comes out as numbers and some come out as text. Any ideas.
Thanks,
22 February 2007, 2:26 pmGary Bouwman:
One more thing to add. If you want to programatically check the error status using VB, try this.
‘ iTop = first data row
‘ iCol = first data column
If oExcel.Range(Cells(iTop, iCol), Cells(iTop, iCol)).Errors(xlNumberAsText).Value Then
22 February 2007, 6:35 pmmsgbox “Cell ” & activecell.address & ” contains a number stored as text”
end if
Aoya:
If you import data and find the numbers are in text format, in most cases if you use the method
23 February 2007, 4:11 am“1. Copy a Blank Cell (or a cell containing the number 0)
2. Select your list of text-numbers
3. Choose Paste Special from the Edit menu.
4. Paste=Values, Operation=Add” it will work, however, it encounter a problem that the method didn’t work if the text numbers have a invisible character in front, example: the number appear as 51.20 in cell A1, if you write a formula =Code(left(A1), it return 160, that means there is a character with code 160 leading the number 51.20., and it’s invisible.
I use to get away by copying the space in front of the number, highlight all the concerned cell, Edit, Find, and Control-V paste to the “find what:” then click “replace all”. Then all the concerned text numbers will be converted to number format.
The quick way you can test whether the number is in text format is by trying and see if the currency style work on the cell. equally you can test with comma style, euro style or percent style.
After a long time I had been using the ” find - replace” method, I had come across a better way.
I am using the data for a chain of calculation in Excel, if the text number from the data I imported is in CELL A1, and I want to a readily converted number in CELL C1, I write a formula =value(Mid(A1,2,10) and now the text number is converted in number format in Cell C1 reaily for me to use, and all other formulas in the file using data from cell C1, work away instantly.
Note: in the formula =Value(Mid(A1,2,10), you can change 10 to a bigger number, if the length of the number is longer than 10, if I change 10 to 1000, the formula will still work.
Andy:
Tks a lot!
You saved my problem: the possibility of pressing that ‘green triangle’ button 5000 times! (the number of workers here).
This certainly would come in handy any other time.
Again, thanks!
8 March 2007, 8:37 pmScott Smith:
Programatically
Dim x_rng as range
set x_rng = [RANGE]
For Each y In x_Rng.Cells
10 March 2007, 3:52 amIf y.Errors.Item(xlNumberAsText).Value = True Then
y.Errors(xlNumberAsText).Ignore = True
End If
Next
mike:
hi guys
24 May 2007, 9:51 pmI am trying to format a cell that can increase the number by one each time the worksheet is open
Just wondering how i would go about it.
would appreciate any one could give me a hint
kevotheclone:
To “Gary Bouwman” on “22 February 2007, 6:35 pm” & “Scott Smith”… Thanks!
I just checked back in today, after imported some data downloaded from our mainframe into Excel and was greeted with thousands of Green Triangles.
Thanks guys, for pointing our the .Errors collection of the Range object. I don’t how I missed that before. That’s exactly what I was looking for.
20 July 2007, 1:34 pmHui...:
Mike
Save this in the Workbook Open module
Private Sub Workbook_Open()
21 July 2007, 2:02 amRange(”A1″).Value = Range(”A1″).Value + 1
End Sub
Mavryk:
For me, I only need to do this to a single column at a time and doing it for all numbers may mess up other references. I use this without problems.
Sub ConvertText2Number()
With Columns(Selection.Column)
.Copy
.TextToColumns Destination:=Range(Cells(1, Selection.Column).Address)
End With
End Sub
2 August 2007, 1:34 pmAndriy:
Guys, I ran into the same problem on ASP.NET and C#. I simply used TextToColumns member funaction of the Range object and it solved all the problems.
8 August 2007, 9:53 pmBJ:
Hi.. Can you suggest a way in Excel or via a macro, that can easily convert a column of text(representing time periods)into Excel time format hh:mm:ss - e.g. per sample text data below:
1hr 39mins 28secs
17 August 2007, 11:06 pm2mins 1sec
1min 45secs
1day 21hrs 45mins 11secs
25secs
2hrs 54mins 46secs
Alice:
Can someone help me import this (from SAS program) into excel without losing leading zeros using “text to columns” feature? Thanks
30 August 2007, 1:55 pmdiag Frequency Percent Cum Freq Cum Percent
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
0 6 0.00 38 0.00
000 11 0.00 49 0.00
00000 282 0.03 331 0.03
00002 3 0.00 334 0.03
0001 4 0.00 338 0.03
0003 1 0.00 339 0.03
00099 2 0.00 341 0.03
0013 1 0.00 342 0.03
0014 1 0.00 343 0.03
00479 1 0.00 344 0.03
0055 1 0.00 345 0.03
00550 1 0.00 346 0.03
00880 2 0.00 348 0.03
00904 1 0.00 349 0.03
010 13 0.00 362 0.03
0102 9 0.00 371 0.03
01020 33 0.00 404 0.04
01023 5 0.00 409 0.04
01024 16 0.00 425 0.04
0103 4 0.00 429 0.04
01030 17 0.00 446 0.04
01032 2 0.00 448 0.04
Jon Peltier:
Text to Columns
31 August 2007, 5:28 amStep 1: Delimited
Step 2: Space character
Step 3: First column, Column data format: Text
Keep the rest of the columns general.
Michael:
Hi BJ -
Here's a UDF that seems to work. You didn't specify what to do with "Days" so I turned them into 24-hour-additions to hours.
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim timearray() As String
Dim Units(4) As String
Dim Values(4) As Variant
Units(1) = "day"
Units(2) = "hr"
Units(3) = "min"
Units(4) = "sec"
timearray = Split(timestring, Chr(32))
For i = 1 To 4
For j = LBound(timearray) To UBound(timearray)
If InStr(1, timearray(j), Units(i), vbTextCompare) Then
For k = 1 To Len(timearray(j))
If Mid(timearray(j), k, 1) Like "[0-9]" Then
Values(i) = Values(i) & Mid(timearray(j), k, 1)
End If
Next k
End If
Next j
If Values(i) = vbNullString Then Values(i) = 0
Next i
Times = Format((Values(1) * 24) + Values(2), "00") _
& ":" & Format(Values(3), "00") _
& ":" & Format(Values(4), "00")
End Function
It returns
01:39:28
00:02:01
00:01:45
45:45:11
00:00:25
02:54:46
for your examples.
...mrt
5 September 2007, 3:10 pmBill:
The text to number trick is a great timesaver. You really helped save a lot of time and headaches. Thanks!
7 September 2007, 8:16 amBJ:
Many thanks Michael, loaded as UDF, but encountered Syntax error on this line of code:
13 September 2007, 10:36 amValues(i) = Values(i) & Mid(timearray(j), k, 1)
Am I missing something?
BJ
Joyce:
Often the numbers I import have a leading ZERO which, sadly, is dropped during the import. I then have to concatenate to add the ZERO back to the front. If I "convert to number" the ZERO drops off again! How can I keep the leading zero and NOT have the green triange?
21 September 2007, 11:28 amMichael:
BJ -
Sorry to be so long to see your reply. I'm not sure what the problem might be...as they always say: "works for me"
However, since it was the ampersand in my pasted-in code that got munged into something else, and that's the line where you're having problems, I'd try to delete that line and then manually retype it. I've had the VBA editor complain about ampersands, and the complaints go away after I manually insert the abutting spaces.
Let me hear back.
21 September 2007, 1:54 pmMichael:
Joyce -
If the text you import is, for instance, zip-codes, and it starts as 01234, and Excel helps you out to make it 1234, either number format the column as "00000" or change your concatenation to =TEXT(A1,"00000")
Adjust the number of zeros to suit.
...Michael
21 September 2007, 2:14 pmColm Byrne:
Getting rid of the green bananna or
Keeping numbers as text - intentionally.
Here's something that works.
I needed to keep the numbers as text because they are customer codes etc and should be left adjusted alpha characters. I insert a non-breaking space right after the number. Excel then interprets the value as character.
Paste the code below into a macro. Select a column, range of cells, row or whatever numbers you need to change and run the macro.
Sub greenbananna()
'gets rid of green triangle for numbers that you intentionally store as text e.g. customer numbers -Colm Byrne
Dim cell As Range
For Each cell In Selection
cell.Value = cell.Value + ChrW(160)
Next cell
End Sub
If you don't know VBA. Just record any macro, then edit it from the Macros menu. This will bring up the VBA editor with the macro you just recorded. Copy and paste over the entire macro. Save.
Go back to excel, select the column or area of numbers you wish to changte, click on Macros, select 'green banannas' and watch the magic
Colm Byrne
10 November 2007, 7:57 pmMatt:
Alice,
15 November 2007, 1:43 pmbringing mainframe SAS to excel without losing leading zeros, see the following link:
http://www2.sas.com/proceedings/sugi28/052-28.pdf
Page 3.
Otis:
My cell phone provider gives me an XML report with the time in the format below. I want to convert it to a 24-hour format for use in pivot tables, as shown in the second column. I've been using a long IF statement and parsing with string functions. But is there an easier, more elegant way to go? Thanks!
Text Result
18 November 2007, 2:32 pmfrom import displayed
12:30A 0:30
10:37A 10:37
12:15P 12:15
2:34P 14:34
Owen:
the best solution! so easy. thanx
27 November 2007, 9:41 pmT-Bonde:
Lori - great tip and time saver - thanks
15 February 2008, 11:37 amJorge Andrés:
Hi all! The solution I found is:
In another column type a sum formula with CERO. This new value will be 'number' format. "=A1+0". In office 2007, even if A1 is a number with text format the sum will be understood and the resulting value a "number" format. At this moment you can manipulate the values as numbers.
Hope it works to you !
16 February 2008, 9:16 amØverdr!veX.:
Numbers stored as text with "0"
402 ---> 0402
Sub ConvertToTextwith0()
26 February 2008, 12:04 pm'Converts numbers stored as text with zeros ahead
Dim cell As Range
Selection.NumberFormat = "@"
For Each cell In Selection
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
cell.Value = "0" & (cell.Value)
End If
Next cell
End Sub
Stawn:
OMG thank you! Such an annoying thing, getting rid of those numbers stored as text! You are awesome.
27 February 2008, 7:34 amMichael:
Hi Otis -
Don't know that this is better than what you have, but this does work:
=IF(RIGHT(A1,1) ="A",TIMEVALUE(LEFT(A1,LEN(A1)-1)&" AM"),
TIMEVALUE(LEFT(A1,LEN(A1)-1)&" PM"))
Then format for 24-hour time.
...Michael
27 February 2008, 4:25 pmMichael:
Hi Otis -
The things you think of while driving home. Here's a version with no IF()'s, and nested SUBSTITUTE()'s:
=TIMEVALLUE(SUBSTITUTE(SUBSTITUTE(A1,"P"," PM"),"A"," AM")))
And it's shorter. Format as 24-hour time.
...Michael
27 February 2008, 8:20 pmMichael:
Hi Otis -
Tried to post this last night, but it didn't stick. Here's another way without any IF()'s
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"A"," AM"),"P"," PM"))
It's shorter. Again, format as 24-hour time.
...Michael
28 February 2008, 6:55 amOtis:
Michael --
Yes, this works wonderfully. Even though I knew of TIMEVALUE, I'd never played with SUBSITUTE. Nice solution. Thanks so much.
--Otis
3 March 2008, 10:03 amsavBill:
This is a good method to convert dates that are 'Numbers Stored as Text' to Date formats using VB. If you have a column with mixed numeric and text data, Excel handles the formatting of the Text values without causing an errors. In the sample text data; 'n/a' would not change, Numbers would convert to the respective date, 'm/d' would convert to the date and current year date format. Its and adaption of Øverdr!veX. Example and other examples here but very simple solution to convert dates that may be in text format.
----------
Ex: data Mixed Date
n/a
n/a
39503
39503
2/29
3/3
3/7
3/17
39517
-----------
Sub ConvertToDateFormat()
'
' ConvertToDateFormat Macro
' Macro Created 3/12/2008
'
For Each Cell In Selection.Cells
'
Cell.NumberFormat = "m/d/yyyy"
Cell.Value = Cell.Value
Next
End Sub
12 March 2008, 8:43 amravi:
hai hell this is excel formula
24 March 2008, 12:04 pmDaniel:
I have to say this is an awesome thread, even though there's a lot of information out there. People who are so concerned with data make me smile.
My particular problem is I'm trying to display values without green triangles for users. All I want to do is get rid of the triangles -- as long as the value LOOKS the same as it did before. Its all about killing the triangle!!
My favourite solution: VBA method for the activecell using the Errors object (thanks to Gary Bouman and Scott Smith) I shortened it into a single line to make it clear
If ActiveCell.Errors.item(xlNumberAsText).value = True Then ActiveCell.Errors(xlNumberAsText).Ignore = True
you can change the object Activecell to anything you want. That's what I'm doing
Thanks people!
14 May 2008, 5:09 pmray:
The root cause is IMPORTED NUMBERS get evaluated text. Is there some way to prevent them from being evaluated that way?? My case is where blanks are imported as zero. zeros get evaluated as text. I have hundreds of spreadsheets and need to solve the root cause, not symptoms. One thing that works is to have it imported as $0, and its a number, although in the wrong format, it can be used in calculations
2 June 2008, 10:04 amAlec:
We have a database program that you can query out certain barcodes.
1. highlight all barcodes
2. copy the numbers
3. go to excel
4. 'right click'
5. 'paste special'
6. 'text'
It automatically formatted them as numbers. Before, it was copying over as html.
2 July 2008, 11:44 amAlex I:
Matt H you're the man! It WORKED! Used that macro with my other ones and am able to format this large health care providers database reports.
31 July 2008, 9:40 pmtom u:
HI all. I had a similar problem when saving excel files to dbf files to allow import into ESRI arcmap. Specifically, even though I would change the formats of numbers stored as text back to numbers (with and without locked formats, locked sheet etc. under security settings), when I saved file and reopened, voila, some columns appeared again as numbers saved as text. Finally, I noticed that this only happened for data where the second line of data was blank (the line after the header row). By putting dummy data in for those lines I solved the problem....once saved as a number it stayed a number on reopening. The nearest I can figure is that excel uses the first data row following the header row to determine format for that column and this stupid trait cannot be fixed.
3 September 2008, 5:03 pm