force Excel to treat numbers as text

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

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

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

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

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


The paste should put this data into 2 rows, 12 columns in WYSIWY
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

--
GottaRu
-----------------------------------------------------------------------
GottaRun's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3129
View this thread: http://www.excelforum.com/showthread.php?threadid=52395

0
3/18/2006 9:39:05 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
545 Views

Similar Articles

[PageSpeed] 59

if it is pasting into a single column you can use the data..text t
columns menu to split it out.
use the options space delimited, the select all columns and state a
text then the function will leave all data as text.

hope this help

--
tony 
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=52395

0
3/18/2006 10:11:39 PM
Hi ......,

You can probably fix your data by using  Data,  Text to Columns
   format the columns of the spreadsheet as Text beforehand
   delimited     perhaps   TAB,   perhaps  spaces (treat multiples as one)
   format all columns as Text   within the Wizard (if I understand you correctly)

If after separation into columns,  still messed up,  you could start over
   with transition options turned on,   But don't forget to turn the transition\
   options off afterwards, or you will have lots of problems with date and
   time entry, as well as use of shortcuts. .

What browser are you copying from,  if it is not  IE 6  you will probably
   have a problem.

What is the source  web page,  if available to anyone.

What is your version of Excel.

You posted from ExcelForum,  so exactly what did you search
   ExcelForum,          limited to web pages debauched by a "forum"
   Google (web search),    unfortunately includes contamination by ExcelForum
   Google Groups,     this is where newsgroup postings appear
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"GottaRun" <GottaRun.24vx8m_1142718001.9705@excelforum-nospam.com> wrote in message
news:GottaRun.24vx8m_1142718001.9705@excelforum-nospam.com...
>
> 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?
>
>
> -- 
> GottaRun
> ------------------------------------------------------------------------
> GottaRun's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31297
> View this thread: http://www.excelforum.com/showthread.php?threadid=523957
>


0
3/18/2006 10:11:46 PM
Reply:

Similar Artilces:

Emailing in excel 2003 01-29-10
Hi all Guru, I have created a button which emails the addresses as shown below, what i would prefer if i could have it so that it emails what ever email address is in cell B10, instead of having set email addresses. Private Sub CommandButton1_Click() ActiveWorkbook.Save Dim Response As String Dim DefaultFolder As String, DefaultFileName As String Dim FileToSave Dim OutApp As Object 'this emails operations manager Dim OutMail As Object Dim strbody As String Response = MsgBox("Are you sure you want to Approve this PIP?", _ vbYesNo + vbInformatio...

Excel Macro Question #4
Hi All, I'm dealing with an Excel issue that's driving me crazy and I was hoping someone could help me out with. I'm working on a spreadsheet to track projects and I would like to create a macro that would track and show the number of a days a project has been opened for each time the spreadsheet is viewed or opened. If anyone can offer any assistance, I would greatly appreciate it! -Tommy Any reason you can't use a formula? Perhaps something like =TODAY()-A1 where A1 contains the date the project opened? In article <1176341869.342666.102220@e65g2000hsc.googlegr...

how do I copy several lines of word text into one excel cell?
In a word doc I need to copy several lines of text and paste them into one excel cell. I can't seem to find any help telling me how to do this (each line pastes into a different cell) You can either double click the cell or press F2 key to get into edit mode for the cell, then the text pasted would all go into the cell. -Simon "jhh" wrote: > In a word doc I need to copy several lines of text and paste them into one > excel cell. I can't seem to find any help telling me how to do this (each > line pastes into a different cell) thank you SO much Simon CC - ...

text box filter by form
For some reason that I just can't explain, a text box on a form associated with a single primary key field behaves unexpectedly when I do a filter-by-form. Instead of listing all of the records from the source table as choices in combo box mode, I only see "Is Null" and "Is Not Null" as choices. I have an older form from another database that behaves as I hoped this one would, yet there seems to be no property differences between the two text boxes. Can anybody explain what I'm missing please? Having "Is Null" and "Is Not Null" as filt...

Adding in Excel Revised
I have a spreadsheet from Pay pal. It lists many columns and cells. For example in one of the COLUMNS (column D) it lists the words"us postal" which are postal fees I incurred, for debits and their are names listed beneath them as credits for paid sales. The actual numbers show in column H that I need to add up that are currently showing as debits. I do not want to add the credits in column H. Since Column H shows debits and credits- I need to separate these out and add the debits for total ship fees. I want to add up only the "us postal" fees in total IN THE COL...

Excel crashes after copy and paste of charts
Hello, I am using Excel 2002 and Micsosoft Windows 2000 Professional. In my excel file I first create two sheets, one with only one chart an another with two charts, then copy and paste the charts into worddocument. Then I remove the sheets. I do this over and over in loop, and after about 60 times (this varies) I get the message: "Microsoft Excel has encountered a problem and needs to close. We ar sorry for the inconvenience" and then Excel crashes. Any suggestions about how to solve this problem? Thank you, Sir -- Message posted from http://www.ExcelForum.com ...

Opening excel files
Hi everyone If I open an excel document from my documents, or other location, excel opens but then the file does not open. I can open excel files from excel and using the open command. but not staright from my documents. Any ideas? thanks Sometimes one of these works when you're having trouble with double clicking on the file in windows explorer: Tools|Options|General|Ignore other applications (uncheck it) --- or --- Close Excel and Windows Start Button|Run excel /unregserver then Windows Start Button|Run excel /regserver The /unregserver & /regserver stuff resets the win...

An issue when sorting in excel PivotTable
I designed a PivotTable in Excel 2003. Data source is a cube in Analysis Server 2000. I want to sort a measure. e.g. top 10. however, relevant to dimmension has many levels. E.g. level1--->Group (i.e. a, b, c,.....z), Level2---->UserName. it works fine if I sort & top 10 by Group. but an issue will pop up if I sort top 10 by usernamea and hide level group. it will list greater than 10 usernames. how can I get accurate results by sort & top 10. Are there some options to support or fix it in Excel PivotTable? thanks. -- Developer QA Dashboard Microsoft China Development Cen...

Excel 2007 will not start.... all of a sudden.. pls help
Hi, I have Office 2007... All modules were working fine on clicking icons of word,access,excel etc. All of a sudden to day I can not start.. excel.. other programs .. starts. on starting excel. clicking Icon... I get message as follow. " This file does not have a program associated with ith it for performing this actoion. Create an association with set association control Panel. " Also when With window explorer I go in office12 folder clicking excel EXE file doe not start error message this is not win32 file. can somebody help.. or do I have to reinstall Office 2007 ...

Excel 2002 Pivot Table Protection
I've been playing around with protecting my pivot table - so far, I can't get it quite right. What I'm hoping to do is allow users to refresh the pivot table and update any of the "page" dimensions, but NOT allow anyone to pivot or manipulate the row and column dimensions. Is this possible? You could use programming to restrict the pivot table use. For example: '========================================= Sub RestrictPivotTableExceptPage() Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveSheet.PivotTables(1) With pt .EnableWizard = False .EnableDril...

Voiding or Deleting Tracking Number
The shipping system correctly inserts a record with the tracking number into the SOP10107 table when a shipment is processed. But if the shipment is later voided I want to delete the record. Is there any reason I should not delete the record from the table? The sql looks fine, but the record does not delete. Is there anything special I should look for? You didnot list the SQL statement so I cannot comment on it. If the transaction has not been posted, you should be able to open the document in the application and delete the tracking number from there. If it is already posted, you ca...

count number of rows in 2 worksheets
HI, Anyone can help? I need a macro code to count number of used rows in two seperate worksheets and compare. If the number do not match error message shall appear "Sheet1 has (blank) number and Sheet2 has (blank) number". (blank) being the number of used rows per sheet. Can this be done? thanks! You have some answers in your first posting of this question. However, I'd like to tell you that your question is not defined very well. You have to tell us what you mean by "used rows". For example, if there are blank rows inside your data, are they to be coun...

Text to Columns 05-28-10
I have a column with the following data: A RUT 212874790014 Each cell has the word RUT, and the number changes, altough it always has 12 digits. I need to have the word in one cell and the number in another, so I use the text to column option. But when I finish doing this the result is: A B RUT 21,2875E+11 How can I avoid this, so as to keep displaying the whole original number in the cell? Thanks in advance. Regards, Emece.- After performing the text to columns, format the number column as 'number' zero decimal points. "Emece&quo...

text recognition difference b/w commercial and non-commercial?
I have OneNotes 2007 installed at work on Windows 7 and text recognition works. I have it installed at home (non-commercial) on Windows 7 and I do not get text recognition. Both are SP2. But they look different. Does text recognition not work in the non-commercial version? This is the only thing that make OneNote worthwhile as a tool for me. MarkC wrote: >I have OneNotes 2007 installed at work on Windows 7 and text > recognition works. I have it installed at home (non-commercial) on > Windows 7 and I do not get text recognition. Both are SP2. But they > look d...

excel based budgeting #3
what is product id 1878 excel based budgeting It enables you to export data to spreadsheets and import data from spreadsheets to budgets. You can find it on the Cards >> Financial menu under Budgets. -- Charles Allen, MVP "Max" wrote: > what is product id 1878 excel based budgeting ...

gridlines from excel worksheet not appearing in print?
im trying to make outlook print gridlines. i copy a piece of an excel worksheet into an outlook email. the gridlines appear ok. but when i go to print- they dont appear on the preview or the print out. also when i paste the same clipboard contents into wordpad and select print preview, the gridlines work! the only way i can figure how to get the gridlines to work in outlook is to paste special and select "microsoft excel worksheet". but the people here are pedantic, and want to be able to just paste the worksheet, and for it to have the gridlines visible on the print. (like...

Upgrade Excel 5.0
I currently have Excel 5.0. Is it possible to purchase upgrades for this version? I would like to upgrade it to at least Excel 97 but preferably further. Thanks. Hi Elizabeth 5 is old. Old like vintage. I don't think upgade licenses go more than a version or two back, so 5 wouldn't qualify for that. Worse perhaps is that only the current version is for sale at any time. That would be Excel XP for a few more days, until 2003 version arrives. You could always check the secondhand market and online auctions like www.ebay.com for older versions. There are a few new cool things in ea...

converting to UNICODE, _TCHAR and TCHAR, writing text files
Hi, i'm converting a MFC application to support unicode. I have some (probably noob) questions: - What is the difference between the types _TCHAR and TCHAR ? I see some UNICODE applications use _TCHAR and others TCHAR. - I have to convert the way textfiles are written. These text-files are send to machines using parallel port, so they have to stay the same as before (when my application had no _UNICODE preprocessor definition). The commands that are used to write these files are fputs, fopen, etc. When changing it to support wide characters are the textfiles still the same? example (_U...

Excel Query
HI, I have a doubt in excel pivot tables. I have "Year" dimension in Page Area. Month and Week date dimension in Row area. There are some old data is displaying in week date. I dont want to change the pivot table design for this as data is huge. Want to know if a macro can help in this matter. Like If i select YEar dimension in the page area as "2004" the weekdate should only have data of 2004. Example : Below is the current setup YEAR 2004 MONTH WEEKDATE AMT JUNE 01/06/2003 0 06/06/2004 56 11/06/2003 0 11/06/2004 78 JULY 01/07/2003 0 06/07/2004 34 11/0...

Excel Cell Format for Numberic Values
When I export data having 20 numeric characters, Excel will put in place a scientifc equation. The numbers are rounded off after 15 characters. Cannot get Excel to display all 20 numeric numbers. Any ideas on how to get Excel to read all 20 numbers in a cell? Hi not possible. Excel only supports 15 significant digits -- Regards Frank Kabel Frankfurt, Germany "Al" <Al@discussions.microsoft.com> schrieb im Newsbeitrag news:4324528D-BBAB-493C-A1B0-EC108BCB6571@microsoft.com... > When I export data having 20 numeric characters, Excel will put in place a > scientifc e...

In excel can you select certain cells which contain the same text
If I have several cells with the same text in them can I filter these out and select them. I know you can do this for formula etc but can it be done for text? try data>filter>autofilter -- Don Guillett SalesAid Software donaldb@281.com "ade" <ade@discussions.microsoft.com> wrote in message news:937AD9E1-668B-4E32-B194-29146DF0A60A@microsoft.com... > If I have several cells with the same text in them can I filter these out and > select them. I know you can do this for formula etc but can it be done for > text? ...

Returns & lot numbers
Version: GP 8, SP 3 How does one do a purchasing return for an item on an invoice that has already been paid? How does one change a lot number on a received item when one has mistyped the lot number and the transaction has been posted? Thank you, M. E. Houston ...

Excel 2002 switches video mode on startup
Office Xp runs fine except starting Excel switches to 640x480, with toolbar icons much larger than normal. The screen momentarily goes black when switching from 800x600 and the comes up in the 640x480 mode. Have uninstalled Office XP and reinstalled but effect is still there. Word, Powerpoint etc work fine. A macro virus? Have deleted personal.xls and excel.tlb but no good. I've never heard of this problem, but you might try some of the steps at www.cpearson.com/excel/startuperrors.htm to narrow down the cause of the problem. -- Cordially, Chip Pearson Microsoft MVP - Excel Pear...

Next Check Number Upon Company Setup
Hello: I have created a new company within my client's Great Plains 8.0 install. This includes the creation of a checkbook in Checkbook Maintenance, of course. I am concerned about the Next Check Number field. I remember that, when I first implemented Great Plains at this client and with their first company, the first payables check run grabbed all check numbers up through the check number specified in the Next Check Number field. I think the number was 100 and Great Plains printed checks for numbers 1-99 installed of starting at 100 and moving forward from there. I want to kno...

Date turns to a number
Hi I have a text field in a report with =Date() as the control source and format as medium date, the problem I have is when I send the report to Excel it turns the date into a number how do I stop this happening? Thanks Bob Just format the cell as a Date. Under the covers, VBA dates are eight byte floating point numbers, where the integer portion is the date as the number of days relative to 30 Dec, 1899 and the decimal portion is the time as a fraction of a day. Today is 27 Oct, 2007: if you ask Access for Format(Date, "0"), it will return 39382. If you plug that number ...