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.

Posted in Uncategorized

133 thoughts on “Number Stored as Text

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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,..).

  6. 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.

  7. 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?

  8. 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

  9. 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

  10. Thank you Rob, and Charlie III. You guys saved me the major headache of manually handling a load of data.

  11. 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

  12. 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.

  13. […] 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) […]

  14. 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.

  15. 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.

  16. 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

  17. 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!

  18. 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

  19. 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.

  20. 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.

  21. 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

  22. 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

  23. Yes it’s clearer and to allow for more digits replace the second line by:
    If IsNumeric(Cell.Value) Then

  24. 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

  25. 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.

  26. 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.

  27. 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.

  28. 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.

  29. 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.

  30. 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.

  31. 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,

  32. 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

  33. 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.

  34. 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!

  35. 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

  36. 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

  37. 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.

  38. Mike

    Save this in the Workbook Open module

    Private Sub Workbook_Open()
    Range(“A1?).Value = Range(“A1?).Value + 1
    End Sub

  39. 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

  40. 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.

  41. 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

  42. 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

  43. 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.

  44. 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.

    Function Times(timestring As String) As String
       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

  45. The text to number trick is a great timesaver. You really helped save a lot of time and headaches. Thanks!

  46. 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

  47. 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?

  48. 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.

  49. 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

  50. 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

  51. 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

  52. 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 !

  53. 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

  54. 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

  55. 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

  56. 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

  57. Michael –

    Yes, this works wonderfully. Even though I knew of TIMEVALUE, I’d never played with SUBSITUTE. Nice solution. Thanks so much.

    –Otis

  58. 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

  59. 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!

  60. 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

  61. 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.

  62. 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.

  63. 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.

  64. 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…

  65. 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.

  66. 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

  67. 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.

  68. 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

  69. Mark: They may not be spaces. Do this

    =ASC(LEFT(A1,1))

    If it returns something other than 32, it’s not a space. In that case, use

    SUBSTITUTE

    and

    CHAR

    get rid of them.

  70. 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

  71. 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

  72. 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

  73. 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

  74. 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

  75. 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

  76. 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..

  77. 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!

  78. 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

  79. 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.

  80. Dick –

    With Substitute, formatted as Date, will do

    =DATEVALUE(SUBSTITUTE(A1,” “,”, “,2))

    Putting in a comma-space for the 2nd space

    …mrt

  81. Thanks to all your suggestions!

    I tried it all and the fastest is either using the text to column or date-value substitute. :)

  82. 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.

  83. Hi All,
    I have another formula for convert text to number

    =value(text)

    Regards,
    Ajay

  84. This is how it’s done, for most normal situations (ie, not French users).

    Public Sub ConvertNumbersStoredAsText(WSName$)
     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
  85. 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

  86. 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!

  87. 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.

  88. @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)

  89. 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:

    Function xyz(Anyvalue As Variant) As Variant

    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

  90. 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.

  91. 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.

  92. 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”

  93. 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

  94. Hi Gora,

    This is easy, just reduce the font size or increase the column width till the full number appears.

    Mark

  95. 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!))

  96. 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

  97. Tom –
    Assuming A1 has $7.60, then B1=A1*1. Format number to 2 decimal places.
    Alternatively could be B1=A1+0.

    …mrt

  98. Thanks Michael
    But had already tried this but no good.
    It just see it as text and the result is #VALUE

  99. @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.

  100. 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.

  101. ‘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

  102. @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

  103. 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!!!!!

  104. 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..

  105. 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?

  106. 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 long
    Dim endBottom As Long

    endRight = Range("B1").End(xlRight).Column
    endBottom = Range("B1").End(xlDown).Row
    Range(Range("B1"), Cells(endBottom,endRight)).Select


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.