Free Microsoft Excel 2013 Quick Reference

force Excel to treat numbers as text

Sorry I searched this and no one seems to have a solution. I tried pre-defiming columns as text but it gets over-riden by the paste. Paste Special doesn't do the trick..

paste special as text on tabular data coming from a web site puts most of the data in the first column. It no longer spreads the columns of pasted data into its own excel columns

the tabular data, tab delimited, I want to 'copy&paste' looks like this:

1) OGRODOWICZ MARK M47 6306 BROOKLYN NY 533 446 41 24:00 23:25 7:33 20:58

2) CHAO DUSTIN M35 3738 NEW YORK NY 534 447 155 24:02 23:47 7:40 23:16

The paste should put this data into 2 rows, 12 columns in WYSIWYG format. All data is text.

One problem is Excel insists putting in 24:02:00 rater than 24:02.
The other problem Excel sees that and 7:40 as some kind of date/time.

Is this a lost cause?


The automatic field format detection is really annoying. I want to do text
manipulation techniques on numbers and excel is just working against. Why
can't I format a cell containing a number as text ? Or do I miss something ?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

How do you do this, and why does excel fight it so hard?

Our payroll provider sends us our payroll data in an excel spreadsheet.
Project numbers that have been charged to come back in the spreadsheet with
an apostrophe (') leading the number, forcing it to be recognized as text.
Up to a point. If I edit a project number that was entered wrong, even
though I keep the apostrophe before the number, excel now treats the cell
value differently. In the body of the spreadsheet it now looks like a
number, with no apostrophe, while the edit box at the top of the spreadsheet
still shows the apostrophe in front of the number. When I try to import the
spreadsheet into MS Access using VBA and Transferspreadsheet, the leading
apostrophe is gone and subsequent processing, expecting the apostrophe,
fails! Even formatting the excel column as 'text' fails to keep the
apostrophe when I transfer the data! Any ideas how to keep excel from
trashing my data, how to get it to give me what I want, and not what MS
programmers think I want instead?

Thanks

Fred

hi all, what function to use to make the number as text and left justified?
normally we put a ' at the cell before the number to treat as text. i do not
want to put each cell with ' as i got more than 1000 records.
thanks alot.
rgds

10430745
530056
3453
RHBB379124
330338
450530192

Hello everyone,

I have a simple tracking sheet that we have people entering overtime
usage in to. I am trying to summarize this info with a basic
pivotchart and group it by month, day, trade, etc.

Anyway, there is a column for inputing the date in to. These dates
have been entered as D/M/YYYY. The trouble is, Excel reads this as M/D/
YYYY, so where 11/4/2008 is intended to be interpreted as the 11th of
April, Excel is reading it as November the 4th.

This results in an error whenever the "Day" exceeds 12, and so I can
not group the results.

Questions:
1) Is there a way to force excel to interpret a text input date in the
format I desire? Such as, "read 11/4/2008 as D/M/YYYY"?
2) If no to the above, I can probably parse the text to create the
decimal value Excel uses for dates (where the 5th of May 2008 = 39573
for example), except I do not know how to arrive at 39573 from
5/5/2008. Any help here would also be appreciated.

I'll keep working. Thanks in advance!

Hello,

I have an excel workbook which execute SQL Insert/update query.
The query adds data to a target worksheet in a target workbook.

in the SQL query:
String values are wrapped with ' symbols
Dates values are wrapped with # symbols
Numeric values are not wrapped

My problem - In target workbook excel displays an error note in the number fields and
notify that "Number stored as Text".

How can I avoid excel to modify numbers as text when using SQL update/insert statements ?

Thank you for your help

hello

I have created a task planner userform with comboboxes as selectors for week numbers. I have set the condition that the target completion week combobox value must be greater than the start date combobox - however the combobox is treating the week numbers as text and therefore week 12 is saying it is smaller than week 8 - I would assume that I would have to set the format of the comboboxes in Userform Initialise sub to number format - can anyone tell me how I would do this.

I have a range that will be appended that I want to convert from a
formula to a value. The formula is a concatenation of part of one row &
the contents of another. All the contents are numbers. I want to keep
them as text to preserve leading zeros/number of characters (I
use.value=.value in other situations) because of some things that
happen further down the line.

I recorded this, and it works - I would just like to get it as clean as
possible.

Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks in advance!

I need to convert numbers as text to values that can be added for a long
column of entries. HELP

Excel is winding me up.

I'm trying to past some html code into a cell and i want excel to treat it
as plain text, not display all the images and special fonts, colours etc
becaue it fouls up the rest of the sheet.

Does anyone know how I might accomplish this?

How to make excel to treat values in cell as a number?

Excel 2003: I have two columns, before I sort I need away of making sure that all legit numbers are seen as numbers not text. I would just like to name the range hit Ctrl+1 and choose number format but that does not always work excel continues to see certain numbers as text. In this case 18141, 134461, 114647 will be aligned to the left even after I highlight them and telling Excel this is a number. I need this because I am sorting and numbers seen as text will be at the top and out of order. The column on the left should be seen as text it is a name. The x and the N/A I can fix so they come up 0.
l645814	x
l1011660	n / a
l1011667	n / a
l1099202	717122
l557997	382094
l644753	18141
l642803	134461
l330677	114647 
l557149	947722
l642802	839040
l1000358	733769
l1011658	634006
Thank you Ken

Hello,
I have a column of identification numbers that look like this:
3-4585
3-4586
3-4587

The format is in date format. (Specifically, category: custom and type: m-yyyy) and that's how it appears in the formula bar. In the cells they appear as above.

I want to excel to treat these numbers as text, not dates. I know i can do this somehow by using an apostrophe but cannot figure out how to implement this?
Can anyone assist?

Thanks,
Nathan

I have created a VBA script that compiles some data for me, but the problem I'm facing is during one of my autofilters that is supposed to be based upon a date range, Excel treats the dates as numbers and thus cannot find anything in the range. I've attached the code and highlighted the line where the problem occurs if anyone can help me force Excel to enter the dates into the autofilter as dates.


	VB:
	
 Main() 
    Dim strFile As Variant 
    Dim SheetCount As Integer 
    Dim dlwb As Workbook 
    Dim datawb As Workbook 
    Dim dlsheet As Worksheet 
    Dim datasheet As Worksheet 
    Dim rnData As Range, rnOrders As Range 
    Dim lnOrders As Integer 
     
    ChDir "V:SHAREDCOMMON.P13PROGRAMSGESandraMy DocumentsEXTRANET DOWNLOADS2005 DOWNLOADS" 
     
    Set datawb = ActiveWorkbook 
     
    strFile = Application.GetOpenFilename("XLS Files (*.xls), *.xls", 0, "Select Download File") 
    If strFile  False Then 
        Set dlwb = Workbooks.Open(strFile) 
    End If 
     
    Set dlsheet = dlwb.Worksheets(1) 
    With dlsheet 
        Set rnData = .UsedRange 
        Set rnOrders = .Range(.Range("AW2"), .Range("AW65536").End(xlUp)) 
    End With 
     
    SheetCount = datawb.Worksheets.Count 
    rnData.AutoFilter Field:=39, Criteria1:="Firm", Operator:=xlOr, Criteria2:="OverDue" 
     
    For i = 1 To SheetCount 
        Set datasheet = datawb.Worksheets(i) 
        With datasheet 
            Range("A6:A19").Copy Range("A5:A18") 
            Range("A7:A18").Select 
            Selection.AutoFill Destination:=Range("A7:A19"), Type:=xlFillDefault 
            Range("C4:O4").Copy Range("B4:N4") 
            Range("C4:N4").Select 
            Selection.AutoFill Destination:=Range("C4:O4"), Type:=xlFillDefault 
            Range("C6:C7").Copy Range("B5:B6") 
            Range("D6:D8").Copy Range("C5:C7") 
            Range("E6:E9").Copy Range("D5:D8") 
            Range("F6:F10").Copy Range("E5:E9") 
            Range("G7:G11").Copy Range("F6:F10") 
            Range("H8:H12").Copy Range("G7:G11") 
            Range("I9:I13").Copy Range("H8:H12") 
            Range("J10:J14").Copy Range("I9:I13") 
            Range("K11:K14").Copy Range("J10:J13") 
            Range("L12:L15").Copy Range("K11:K14") 
            Range("M13:M15").Copy Range("L12:L14") 
            Range("N14:N15").Copy Range("M13:M14") 
            Range("O15").Copy Range("N14") 
            Range("L15:O15").Select 
            Selection.ClearContents 
            Range("J14").Select 
            Selection.ClearContents 
        End With 
         
        rnData.AutoFilter Field:=25, Criteria1:=datawb.Worksheets(i).Cells(2, 1).Value 
        For j = 1 To 4 
            [B]rnData.AutoFilter Field:=38, Criteria1:=">" & Format(datawb.Worksheets(1).Cells(15 + j, 18).Value,
"mm/dd/yy"), Operator:=xlAnd, Criteria2:="

I must import a text file. It contains numbers of varying formats. They
could include a colon and a period or just a colon... zeros at beginning and
end, etc.

I want to retain the format of the varying numbers WHEN IMPORTING a pipe
delimited text file.

How do I force Excel to import all data in the text file as TEXT. Simply
selecting format "Text" for the entire worksheet (before importing) does not
do it - that selection is overwritten.

Please test your resolution before answering.

Is there a way to have excel display a number as text? For example: 5 as Five or 155 as One Hundred Fifty Five

Thanks in advance for any suggestions!

Thanks for any help.
I have copied and pasted some rows of an Access database to an Excel
workbook. Excel pasted the numbers as text. But when a coworker did on her
computer, Excel pasted them as numbers. And a text like (0.1105), negative
0.1105, was pasted as (1), negative one, on my computer. This means I can't
just change the number to text, as the value has been totally changed.
I looked in Tools>Options>Edit to see if there might be something there, but
I couldn't find anything relevant. Any ideas?
I can do Tools>Office Links>Analyze it with Excel, but everytime I do a row,
it wants to open a new file, and I have about 20 non-contiguous rows, so this
is a bit tedious.
Thanks again.

How do you do this, and why does excel fight it so hard?

Our payroll provider sends us our payroll data in an excel spreadsheet.
Project numbers that have been charged to come back in the spreadsheet with
an apostrophe (') leading the number, forcing it to be recognized as text.
Up to a point. If I edit a project number that was entered wrong, even
though I keep the apostrophe before the number, excel now treats the cell
value differently. In the body of the spreadsheet it now looks like a
number, with no apostrophe, while the edit box at the top of the spreadsheet
still shows the apostrophe in front of the number. When I try to import the
spreadsheet into MS Access using VBA and Transferspreadsheet, the leading
apostrophe is gone and subsequent processing, expecting the apostrophe,
fails! Even formatting the excel column as 'text' fails to keep the
apostrophe when I transfer the data! Any ideas how to keep excel from
trashing my data, how to get it to give me what I want, and not what MS
programmers think I want instead?

Thanks

Fred

I must import a text file. It contains numbers of varying formats. They
could include a colon and a period or just a colon... zeros at beginning and
end, etc.

I want to retain the format of the varying numbers WHEN IMPORTING a pipe
delimited text file.

How do I force Excel to import all data in the text file as TEXT. Simply
selecting format "Text" for the entire worksheet (before importing) does not
do it - that selection is overwritten.

Please test your resolution before answering.

Hey all, I am running a web query that's bringing in football spreads in the form of "+7 -110". Excel is treating it as a formula rather than text and adding an equals sign in front of the numbers and then displaying the result in the cell. I want to be able to separate the +7 and the -110 into separate cells automatically but I can't figure out any way to do it. Any ideas? Thanks.

how do I keep 16 digit numbers as text in excel?
I format the cells as text ahead of time but when I cut and paste the
numbers in the last digit is converted to a zero and it is displayed in
scientific notation.

I've got a very strange anomaly going on, in Excel 2007.

It has to do with numbers intended to be formatted as text. Normally, if you want to enter a number formatted as text, you preceed the number with an apostrophe, and Excel interprets that as an intention to store the number as text. When you do this, a little green triangle appears in the upper left corner of the cell, and when you highlight the cell, a little diamond with an '!' mark expands to tell you that the number is stored as text.

However, I've got a spreadsheet with a column on integer numbers which look, on casual examination, to be formatted as text; first of all, the numbers are left-aligned, which is what happens to numbers that are formatted as text. Secondly, when you highlight one of these numbers, and select 'format..'/'format cells...', it shows as text.... yet they aren't actually being interpreted as text, when I access them via my own program (independent of Excel). The only numbers which are being correctly interpreted as text are ones that I entered with the apostrophe....

Another way to format a cell is to highlight the cell, select 'Format...'/'Format cells...', and select 'text' as the format rule.

However, when I try this on a blank spreadsheet, it doesn't work. I enter a number, and it is right-justified, as numbers ordinarily are. If I select the cell, and execute 'Format...'/'Format cells...'/'text', the number becomes left justified.... but it's not really text; no little green triangle, etc.

So, the dilemma is this: why doesn't the 'Format...'/'Format cells...'/'Text' function actually work? Is it possible that there's some sort of optional switch (which I haven't found) that affects the way cell formatting operates?

Anyone?

RE: Format-Retrieve Number as Text with Regular Expression?

This GetNumber UDF (See related link below) does exactly what I asked for, which was to extract a number string and evaluate it as a number.

...........................................
Function GetNumber(s As String)

With CreateObject("vbscript.regexp")
.Pattern = "d+([/.]d+)?"
If .test(s) Then
GetNumber = Evaluate(CStr(.Execute(s)(0)))
Else
GetNumber = "No number found"
End If
End With
End Function
...........................................

I have a formula which gets the LEN(GetNumber(A1)), which does not work with this function if it is a decimal like 1/3Ba2C3 reads as 0.33333333. I can use RegExpFind(A1,"[d/.]{1,}") instead, but I think there must be a way to modify GetNumber to make a similar GetText UDF. I thought I would have a try first but so far I am stumped and it could take a while, so I will post. Thanks in advance.

Re: Format-How to Force Fraction to DECIMAL?
http://www.mrexcel.com/forum/showthread.php?t=295382

Hello

I am loading TXT files into XLT file. TXT files are selected by holding "Shift" and "Enter" on the first file and on the last file. Lets say 1.txt thru 22.txt.
Currently files are loaded in this sequence: 1.txt, 10, 11,12,13,....19, 2, 20,21,22.txt.
How can I force Excel to load 1.txt, 2,3,4,5,6,7,8,9,10,11.....20,21,22.txt??

Here is part of my code which does the sorting during loading.

Code:
   FName = Application.GetOpenFilename("CMM Text Files (*.txt), *.txt", , "Select File(s) to Import", "Import", True)
    
    If UBound(FName) = 0 Then 'FName = False Then
        MsgBox "No files were selected.", vbInformation + vbOKOnly, "No Files to Import"
        Exit Sub
    End If
    
    '*******************
    ' SORT FILENAMES ALPHABETICALLY
    '*******************
    Call sort_array(FName, 1, UBound(FName))
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
     
    Dim justName As String
    Dim ln As Integer
    
    '*******************
    ' LOOP THROUGH FILES
    '*******************
    For inf = 1 To UBound(FName)
        If inf > 1 Then
            '*****************
            ' CREATE NEW SHEET
            '*****************
            Call Add_Report_Sheets
            Application.ScreenUpdating = False
            Set cursheet = ActiveSheet
            If InStr(UCase(ActiveSheet.codename), "SHTMAIN") = 0 Then
                'WRONG ACTIVESHEET IS SELECTED
                Application.ScreenUpdating = True
                MsgBox "There was an error while trying to import the file.", vbExclamation + vbOKOnly, "Could not import
file."
                Exit Sub
                
            End If
                
            ' WAS TURNED OFF AFTER ADD_REPORT_SHEETS()
        End If
        
        ln = Len(FName(inf))
        
        For inx = ln To 1 Step -1
            If Mid(FName(inf), inx, 1) = "" Then
                Exit For
            End If
        Next inx
            
        justName = Right(FName(inf), (ln - inx))
    
        Application.StatusBar = "Importing " & justName
    
        Workbooks.OpenText Filename:=FName(inf), Origin:=xlWindows, StartRow _
            :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(30, 1), Array _
            (33, 1), Array(44, 1), Array(55, 1), Array(66, 1), Array(77, 1), Array(88, 1), Array(99, 1), _
            Array(110, 1), Array(121, 1), Array(132, 1), Array(144, 1), Array(154, 1), Array(165, 1), _
            Array(178, 1))
            
        Set WB = Workbooks(justName)
            
        LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row


I have a column of 10900 cells with dates of the form
13-May-05. Excel automatically interpreted them as
dates when I imported the data from a text file. Now
I want them to be interpreted as text. The problem is:
when I change the format to "text", the cell content
changes to a number. For example, 13-May-05 becomes
38485.

Hindsight being 20-20, I should have entered the dates
in the form '13-May-50. But it is too late for that
now, I think.

How can I change the cell format to the text 13-May-05
without manually changing all 10900 cells?