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.
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.
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.
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.
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.
Great Tip – thanks Lori
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.
– 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,..).
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.
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?
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
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
Thank you Rob, and Charlie III. You guys saved me the major headache of manually handling a load of data.
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
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.
[…] 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) […]
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.
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.
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:
‘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
You should get the Nobel Prize or something for thisI spent at least an hour trying to figure out why my formulas didn’t import values!
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,
Rob
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.
Alan:
The trick is to format the cells as Text before entering the numbers/text.
Rob
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.
Alan: you’re right, my mistake.
You’re likely correct on this.
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)
If Cell.Value = Val(Cell) Then
Cell.NumberFormat = “General”
Cell.Value = “=””” & Cell & “”””
End If
Next Cell
Lori –
Be consistent with your Cell.Values:
For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
If Cell.Value = Val(Cell.Value) Then
Cell.NumberFormat = “General”
Cell.Value = “=””” & Cell.Value & “”””
End If
Next Cell
Yes it’s clearer and to allow for more digits replace the second line by:
If IsNumeric(Cell.Value) Then
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
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.
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.
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.
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
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.
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.
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,
The copy (cell containing 1) and pastespecial solution (multiple) works interactively, but does not convert text to numbers when run via VB.
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,
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
msgbox “Cell ” & activecell.address & ” contains a number stored as text”
end if
If you import data and find the numbers are in text format, in most cases if you use the method
“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.
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!
Programatically
Dim x_rng as range
set x_rng = [RANGE]
For Each y In x_Rng.Cells
If y.Errors.Item(xlNumberAsText).Value = True Then
y.Errors(xlNumberAsText).Ignore = True
End If
Next
hi guys
I 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
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.
Mike
Save this in the Workbook Open module
Private Sub Workbook_Open()
Range(“A1?).Value = Range(“A1?).Value + 1
End Sub
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
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.
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
2mins 1sec
1min 45secs
1day 21hrs 45mins 11secs
25secs
2hrs 54mins 46secs
Can someone help me import this (from SAS program) into excel without losing leading zeros using “text to columns” feature? Thanks
diag 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
Text to Columns
Step 1: Delimited
Step 2: Space character
Step 3: First column, Column data format: Text
Keep the rest of the columns general.
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
The text to number trick is a great timesaver. You really helped save a lot of time and headaches. Thanks!
Many thanks Michael, loaded as UDF, but encountered Syntax error on this line of code:
Values(i) = Values(i) & Mid(timearray(j), k, 1)
Am I missing something?
BJ
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?
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.
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
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
Alice,
bringing mainframe SAS to excel without losing leading zeros, see the following link:
http://www2.sas.com/proceedings/sugi28/052-28.pdf
Page 3.
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
from import displayed
12:30A 0:30
10:37A 10:37
12:15P 12:15
2:34P 14:34
the best solution! so easy. thanx
Lori – great tip and time saver – thanks
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 !
Numbers stored as text with “0?
402 –> 0402
Sub ConvertToTextwith0()
‘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
OMG thank you! Such an annoying thing, getting rid of those numbers stored as text! You are awesome.
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
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
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
Michael
Yes, this works wonderfully. Even though I knew of TIMEVALUE, I’d never played with SUBSITUTE. Nice solution. Thanks so much.
Otis
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
hai hell this is excel formula
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!
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
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.
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.
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.
Hi, had the exact issue generating excel s/s from an ASP application. DISCOVERED FINALLY! The user had added a hypen (read minus sign) into a text field and all numbers stored as text from there. Went into the db, removed the offender and viola!
Hope this helps…
I came up against a stubborn case the other day, exported from an accounting application (SAP) – all the usual methods failed. With figures in millions, I had to remove commas, decimal points and minus signs before it could be converted to number, then having to divide by 100 and multiply be -1 to revert back to the true values.
The macro privided by Matt worked wonderfully, i’d suggest adding that to the article!
Well … I needed to take another look at this issue after some time away on other things. By combining a few of the solutions, I have one that is workable – seems simple when you see it.
sub DoTheWork()
Dim xCell As Range
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
For Each xCell In Selection
If Not IsEmpty(xCell) And IsNumeric(xCell.Text) Then
xCell.Value = (xCell.Text) * 1
End If
Next xCell
End Sub
‘ what a lot of crap to go through to convince XL a number
‘ is actually a number
I am having this problme with Excel:
e.g.,
$339,000Â
$408,900Â
$438,900Â
$468,900Â
$479,000Â
$799,000
Above column is considered as text and not value there is a space in front and at the end of the number
e.g., (space$339,000space)
I tried all ways to switch to value without luck.
=Trim(cellA:1)
=Clean(cellA:1)
But no luck up to know. Any Hints?
Many thanks in Adance for your help.
Mark G.N.
Mark –
Try =TRIM(A1) or =TRIM(A1)*1 or TRIM(A1)+0
All apply math operators to text, and should coerce a number output without changing value.
…mrt
Mark: They may not be spaces. Do this
If it returns something other than 32, it’s not a space. In that case, use
and
get rid of them.
Hi Dick
=ASC(LEFT(A1,1)) is not available on excel?!!
Any other option.
Thanks Micheal for your solution but it is not working
Thanks
Mark
Mark
=Code(Left(a1,1))
Cheers.
John
Hi Mark –
It’s ASC() in VBA, it’s CODE() in XL spreadsheets.
See what =CODE(LEFT(A1,1)) and =CODE(RIGHT(A1,1) reports.
My suspicion is that at least one of them is ascii 160, the non-breaking space.
So, =SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),””),CHAR(32),””) ought to do.
…mrt
I should really test my stuff before I comment. :)
Michael,
Many many thanks you are 100% correct. the second equation is working properly.So, =SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),””),CHAR(32),””) .
Regards and God Bless
Mark
Mark –
I’ll share the blessing with Dick. That’s really what he was trying to get you to do. He just had his head in the wrong game ;-)
…mrt
Michael + Dick,
Ok another question, what is the reverse of the below equation
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),””),CHAR(32),””)
If I like to switch a VALUE to TEXT use the above example?
Regards
Mark N
Mark –
It is much like formatting a number from the menu, except it’s really text.
If you have 339000 in A1, use =TEXT(A1,”$0,0?) to get $330,000 as text.
You put the format you want as the second argument. See the online help for TEXT()
If you want to put the non-breaking space back, then
=TEXT(A1,”$0,0?)&CHAR(160)
Slightly simpler for your original question would be
=TRIM(SUBSTITUTE(A1,CHAR(160),””))
…mrt
Best thread ever !
Daniel:
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
great man, thanks..
I have a problem and this seem to be a great venue to ask.
I need to compute for the age on a list given to me. However, the birthdate came out as text i.e. May 24 1970. Since there is no comma on the date, it is unable to compute the age of person.
Insert comma on each birthday will take forever. Is there a way to make things easier?
Thanks in advance!
Cat –
If A1: May 24 1970
In C1: =MATCH(LEFT(A1,FIND(” “,A1)-1),{“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”},0)
In D1: =VALUE(TRIM(MID(A1,FIND(” “,A1)+1,2)))
In E1: =VALUE(RIGHT(A1,4))
In G1: =DATE(E1,C1,D1)
Fill down.
…mrt
Cat…
If all your text dates are in a single column, try this:
Select the column
Data|Text to columns (xl2003 menus)
Fixed width
but remove any lines that excel guessed and don’t add any of your own.
Choose date, mdy as the format.
And finish up the wizard.
Give that column an unambiguous date format (dd-mmmm-yyyy) and see if all the cells were converted ok.
If they were, then use any date format you like.
Another:
Can we do this in Find and Replace? I can’t figure out a way.
Dick –
With Substitute, formatted as Date, will do
=DATEVALUE(SUBSTITUTE(A1,” “,”, “,2))
Putting in a comma-space for the 2nd space
…mrt
Brilliant Michael. Shame on me for forgetting about the instance_num argument.
Oh, and I can’t do it with Find and Replace, but I can with Text to Columns. There’s a post in the queue.
Thanks to all your suggestions!
I tried it all and the fastest is either using the text to column or date-value substitute. :)
I encountered my text changing to number when I removed the leading “A”. here is an easy solution. use formula:
=RIGHT(A3,LEN(A3)-1) assuem data is in cell A3. this will return the data to the cell with the formula. next format the cell as text. Finally copy paste special values. done.
Hi All,
I have another formula for convert text to number
=value(text)
Regards,
Ajay
Sub ConvertTextToNumbers()
Selection.TextToColumns
Selection.NumberFormat = “0?
End Sub
This is how it’s done, for most normal situations (ie, not French users).
Application.EnableEvents = False ‘prevent triggering event macros
Dim iCell
With ActiveWorkbook.Sheets(WSName) ‘could use a workbook argument instead of activeworkbook
For Each iCell In .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
If iCell.Errors.Item(xlNumberAsText).Value Then
If (Left(iCell.Text, 1) <> “0”) Then iCell.Value = iCell.Value ‘skip cells with leading zeros
End If
Next
End With
Application.EnableEvents = True
End Sub
I have a simple solution to the problem Malcolm and Alan posted way back in September 2006, where Excel shows a green triangle with an error message next to text that is thinks is a number (number stored as text error).
On September 15, 2006 Alan 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).” (emphasis mine)
I am not a programmer so I think if there is a simple solution that works, go for it. I did not see anyone post this solution, but I honestly did not read them all.
This is what worked for me:
1) Select your range of cells with the data that excel is giving you the green error triangle on.
2) At the top of the selected range, you should see the exclamation point (!) indicating the error message. Right click the exclamation point and choose Ignore Error.
This should keep the data as text (e.g. leading zeros preserved) and remove the green error triangle, no code required. I have got this to work in Excel 2003 and 2007.
Hope that helps someone,
Brad
I had the problem of getting numbers as texts in output from a combo box (whose input was actually numbers, and I wanted numbers in output, but it converted the input numbers to texts). And I didn’t want to manually go and change the output format from texts to numbers each time the selection int he combo box was changed. So, taking hint from Charlie’s funtion (first response here), in a new cell, I used the function Value, to convert the combo box output into a number, so any further function calls the value in this new cell rather than the cell which carries the output of the combo box. Thanks Rob & Charlie!
Thanks for this solution this has been driving me crazy. You truly are a wizard
I need to convert a number from dollars and cents to a general number without the period ($55.51 to 5551). I have a macro that does this and it works great except when the 2nd number after the decimal is a zero. It drops the zero. If the number to convert is $55.50 I end up with 555. I think the problem is in excel and not the macro, but I don’t know enough about macros to really make that generalization. If anyone can help with this, I would really, really appreciate it.
Jeanna: Post the macro.
@Jeanna,
If you have a question about a macro you are using, it is always a good idea to show us the macro so we know what you know without having to guess. With that said, let me take a guess. It would appear that you are assigning your dollar and cents value to a numeric variable (numeric variables do not track trailing zeroes after the decimal point, only Strings do that) and then converting that number to a String value and replacing the decimal point with the Empty String. Instead of assigning the value to the numeric variable, try assigning the Int of 100 times the value to the numeric variable… the trailing zero will be preserved and you won’t need to replace the decimal point because there won’t be one. So, if your value is in A1 and your numeric variable is named Cents, then you would use this line of code to do what you want…
Cents = Int(100 * Range(“A1?).Value)
I’m sorry, wasn’t thinking that it might help to post the macro. Rick, I have not had a chance to try your suggestion. Will do so todat. Thank you!
Below is the macro:
Dim Z As Integer
Dim theString As String
Dim currChar As String
Dim temp As String
If IsNull(Anyvalue) Then Exit Function
theString = CStr(Anyvalue)
temp = “”
For Z = 1 To Len(theString) ‘Go through string char by char.
currChar = Mid$(theString, Z, 1) ‘Get the current character.
Select Case currChar ‘If char is a number then retain, else discard
Case 0 To 9
temp = temp & Mid(theString, Z, 1)
Case “,”
temp = temp & Mid(theString, Z, 1)
Case “:”
temp = temp & Mid(theString, Z, 1)
Case Else
End Select
Next Z
xyz = temp
End Function
Great thread, got mine working thanks to Colm Byrne above:
I had a lot of text and numbers in a range, all the numbers had the little triangle. Clicking the exclamation mark only works if the range is all numbers. So I used the below macro slightly altered as I got a type mismatch VBA error:
Sub greenbananna()
Dim cell As Range
For Each cell In Selection
cell.Value = CStr(cell.Value)
Next cell
End Sub
That worked for me perfectly, and am very happy now :)
Needed to format all the range as an excel general type, wont work for excel text type, before running the macro.
Enjoy.
To eliminate the green errors when you WANT your column to be formatted as text,
Select one of the errors
Select Error Checking Options from the popup
Unclick the checkbox by Number stored as text
Click the OK Button.
Thanks Rob, you solved my problem. For this i spent 2 hours a day. Oh!such an easy task it is!! I liked your approach. You have feeded it very intellectually.
http://godwinsblog.cdtech.in/2010/12/microsoft-office-excel-add-in-plug-in.html
Thanks everyone for contributing
Colm Byrne explained macro very well with green banana
Right now I am trying vlookup where sheet A have same columns with sheet B,
but problem is that data in columns have text & numbers mixed … so my formula is returning error or things are not matching up.
Anyone have any idea in this regard?? my exact formula is
=VLOOKUP(B3,Product!A2:H33655,2,FALSE)
B3 have = A123, 6549AKR, 0123, and same is the case on other sheet “product”
Well Sometime when a large data i.e 9564389155 is input in a cell excell returns 1E+10, what is this? I googled but couldn’t find anything so I asked here
Hi Gora,
This is easy, just reduce the font size or increase the column width till the full number appears.
Mark
I have just met a problem where we create reports in Reporting Services, export it to XML, then import the XML into Excel.
Unfortunately, SSRS is seeing all of the dimensional data as text, so it is text in the XML table. This means that if we try and do comparisons in DSUM like
Month to Maturity
>0
<=12
and it just doesn’t work.
The only solution I can think of is to create a schema, adjust that to state a type of integer, and then save that in the XML file directory. Unfortunately, this will amount to a ton of work (try creating a schema for an Excel file, we could only figure it out using InfoPath (sic!))
Hi Guys
I’m sure this is simple but I tried a couple of things but no go.
I need to convert a Text column to numbers
$7.60Â
$6.50Â
Â
$9.00Â
Â
To
7.60
6.50
0
9.00
0
Please advise.
Tom
Tom –
Assuming A1 has $7.60, then B1=A1*1. Format number to 2 decimal places.
Alternatively could be B1=A1+0.
…mrt
Thanks Michael
But had already tried this but no good.
It just see it as text and the result is #VALUE
Tom: Can you post a sample spreadsheet somewhere, and paste a link here? Perhaps post your question and sample spreadsheet here: http://www.excelguru.ca/node/111
@Tom,
If your cells are formatted as Text, the you can convert the cells with “numbers” in them (not the blank cells though) using Text To Columns. Select your column of numbers, then click “Data/Text To Columns” on the menu bar, then click the Finish button… you dollar values should be real numbers now. To convert the blank cells to $0.00, use Replace… the cells you select for Text To Coluumns should still be selected (if not, re-select them)… click Edit/Replace on the menu bar, make sure there is nothing in the “From what” field and put $0.00 into the “Replace with” field and click the “Replace All” button. That should leave you with what you wanted.
Hi Jeff
Yes I’ve attached the prob to the forum you’ve stated
http://www.excelguru.ca/forums/forumdisplay.php?2-Formulas
Under Lyfsabuz “SumA22?
Hope it helps
Thanks
Tom
I’m looking for a method to convert large arrays of numbers stored as text to numbers (in Excel 2007). Text to Columns works, but only one column at a time, and I’d like to perform this conversion automatically. Here’s the situation:
I write Access queries from a large database containing chemical information that includes dates, text, and numerical values. For a number of reasons, the data are stored in Access as text. The numerical columns contain mixed numbers and text. For example, I may have a numerical value for a concentration [e.g. chloride; the number in the column might be 100] or a symbol for below detection [e.g., <5].
From Access I copy-paste, or copy paste-special-CSV into Excel, and then for each column must use Text to Columns to convert numerical values to numbers. Changing the format won’t work, so I’m stuck doing Text to Columns one column at a time for 20-30 columns. This is going to be repeated for about 100 sample sites.
Any ideas? This is the first time I’ve tried a blog site, and thanks for your help.
Joe: Can you put a 1 in a spare cell, copy that cell, select all your data, the Paste – Special – Multiply?
This was the best. Thank you Scott.
Very Very Thank you !!
this way can solve my problem !
‘Will work, just select the range of interest first before running the code
Sub convertTextNumbersToNumericValues() ‘formats numbers formatted as text into numerics, also removes underlying formulae
Dim c As Range
For Each c In Selection
c = c.Value
Next c
End Sub
@Oleksiy
There is no need to loop through all the cells one-at-a-time; this will do exactly what your code does…
Sub ConvertTextNumbersToNumericValues_2()
Selection.Value = Selection.Value
End Sub
However, if the cell is formatted as Text, neither your code nor mine will change the text number to a real number. The following code will handle both kinds of text numbers…
Sub ConvertTextNumbersToNumericValues_3()
Selection.NumberFormat = “General”
Selection.Value = Selection.Value
End Sub
I absolutely love you! YOu saved me sooooo much time, I used the older Excel trick on new Excel and I got all of my “text” numbers into number format!!!!!
Hi,
I am new to this forum, to avoid number stored as text error just uncheck the rules 1, 3,4 & 8 which under the heading Error checking rules under formulas options in Excel options dialog box. If you do that, you dont need to use VBA macro or manually converting things etc..
I’ve tried practically every one of the above solutions but am frustrated they don’t work for me.
I copy/paste data from main frame system into xls sheet. Looks perfect.
Col A holds Book/Chapter refcs of a Tech Manual like this:
8/1
/1
/2
12/1
/1
When I check: “ISTEXT” = TRUE
It appears there’s a blank space at the right end which is fine if it will keep it in tact!
When I run another macro, it converts the 8/1 into “1-Aug” and the “12/1” into “1-Dec” and when
the “ISTEXT” is checked again it = FALSE.
I’ve searched high & low to find vba to convert that “General” data that looks like a date back to TEXT.
When I tried using the “Text to Columns” method using “TEXT” it converts “1-Aug” to 8/1/2014 (which puts the cell type back to TEXT but its NOT the way it originally was and needs to look).
When I tried a function: =SUBSTITUTE(SUBSTITUTE(A23,CHAR(160),””),CHAR(32),””)
This simply converts tha “1-Aug” to “41852” which is again a ‘General’ cell…ISNUMBER=TRUE
When I’ve tried both ‘green banana’ sets of vba, it did nothing…the “1-Aug” remained…
..and as directed, the column was ‘general’ prior to running.
Tried this and again, nothing happened:
Sub Convert()
For Each cell In Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
If cell.Value = Val(cell.Value) Then
cell.NumberFormat = “General”
cell.Value = “=””” & cell.Value & “”””
End If
Next cell
End Sub
Like Malcom & Alan of Sept 2006, I need my text to stay as text, or be converted BACK to the way I orig had it — in the event another set of code converts it unwantedly???
Here’s the code that seems to convert it, is there a way to prevent it from converting column A?
Christopher. This assumes that row 1 is always complete across the entire table and that the same is true for column b down to the bottom of the data.
In Sub Mod_12_BOM2TO, replace Cells.Select at the top with
Dim endRight As longDim endBottom As Long
endRight = Range("B1").End(xlRight).Column
endBottom = Range("B1").End(xlDown).Row
Range(Range("B1"), Cells(endBottom,endRight)).Select
Using C# you can solve like the following…
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range(“a1”, “b1”);
formatRange.NumberFormat = “@”;
xlWorkSheet.Cells[1, 1] = “098”;
Source : http://csharp.net-informations.com/excel/csharp-format-excel.htm
Justin