Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy and paste row as a text within workbook including numbers
Please help,
I need to copy some rows from one worksheet to another, within workbook, and paste them as text including some numbers (currencies) as a string of characters "$13.00". The destination worksheet cells are formatted as Text before the paste operation. In original worksheet cells are formated as text but after the "paste as values" operation, cells are getting converted to "Currency" formatting that strips "$" sign and zeros from two decimal places of the record (in a destination cell I get just number 13). Is there a way to force Excel to treat these entries as a text? By the way, if I include an external workbook in a two step copy-paste process, excel gives an option save as "Text", within workbook I do not get that option. How to work around that? Georgee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy and paste row as a text within workbook including numb
Hi Georgee,
What version of xl are you using. I have tested and re-tested with versions 2002 and 2007 and if formatted as text before copy and paste then they get pasted as text with the $ sign whether I use paste, paste special-values or paste special-formulas. If they are formatted as currency then I cannot get them to paste special-values or paste special-formulas with the $ sign into cells formatted as text. Are you sure that they are not formatted as currency? Regards, OssieMac "GorKo" wrote: Please help, I need to copy some rows from one worksheet to another, within workbook, and paste them as text including some numbers (currencies) as a string of characters "$13.00". The destination worksheet cells are formatted as Text before the paste operation. In original worksheet cells are formated as text but after the "paste as values" operation, cells are getting converted to "Currency" formatting that strips "$" sign and zeros from two decimal places of the record (in a destination cell I get just number 13). Is there a way to force Excel to treat these entries as a text? By the way, if I include an external workbook in a two step copy-paste process, excel gives an option save as "Text", within workbook I do not get that option. How to work around that? Georgee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy and paste row as a text within workbook including numb
On Nov 11, 2:10 am, OssieMac
wrote: Hi Georgee, What version of xl are you using. I have tested and re-tested with versions 2002 and 2007 and if formatted as text before copy and paste then they get pasted as text with the $ sign whether I use paste, paste special-values or paste special-formulas. If they are formatted as currency then I cannot get them to paste special-values or paste special-formulas with the $ sign into cells formatted as text. Are you sure that they are not formatted as currency? Regards, OssieMac "GorKo" wrote: Please help, I need to copy some rows from one worksheet to another, within workbook, and paste them as text including some numbers (currencies) as a string of characters "$13.00". The destination worksheet cells are formatted as Text before the paste operation. In original worksheet cells are formated as text but after the "paste as values" operation, cells are getting converted to "Currency" formatting that strips "$" sign and zeros from two decimal places of the record (in a destination cell I get just number 13). Is there a way to force Excel to treat these entries as a text? By the way, if I include an external workbook in a two step copy-paste process, excel gives an option save as "Text", within workbook I do not get that option. How to work around that? Georgee Interesting, I will check it when I am back in my lab, thanks for reply. G |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy and paste row as a text within workbook including numb
On Nov 11, 2:10 am, OssieMac
wrote: Hi Georgee, What version of xl are you using. I have tested and re-tested with versions 2002 and 2007 and if formatted as text before copy and paste then they get pasted as text with the $ sign whether I use paste, paste special-values or paste special-formulas. If they are formatted as currency then I cannot get them to paste special-values or paste special-formulas with the $ sign into cells formatted as text. Are you sure that they are not formatted as currency? Regards, OssieMac "GorKo" wrote: Please help, I need to copy some rows from one worksheet to another, within workbook, and paste them as text including some numbers (currencies) as a string of characters "$13.00". The destination worksheet cells are formatted as Text before the paste operation. In original worksheet cells are formated as text but after the "paste as values" operation, cells are getting converted to "Currency" formatting that strips "$" sign and zeros from two decimal places of the record (in a destination cell I get just number 13). Is there a way to force Excel to treat these entries as a text? By the way, if I include an external workbook in a two step copy-paste process, excel gives an option save as "Text", within workbook I do not get that option. How to work around that? Georgee Let me clarify: It is Excel 2003. The original worksheet is created by importing a comma delimited text file (report from the database) into blank worksheet with columns formatted as text. After import the formating of cells is being modified depending on some intelligent guess of Excel. Some cells stay text formatted including some spacing reflectingon the size of data in original database, some are converted to currency (if there was just a number a dollar sign in front of it), some that were looking like date 11/07/07 are converted to the Date formatting and displayed as 11/07/2007, if there was a record looking like a deduction for instance: "8 - 3 ", after importing it shows Aug 03. Is there any way to control that? Georgee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy and paste row as a text within workbook including
Hi again Georgee,
Unfortunately I have found that csv files import without giving the user the opportumity to nominate the type of data in each column and Excel often "guesses" wrong. I am interested if anyone has a better solution but the way I handle these is to change the name of the file to .txt via windows explorer. (Answer Yes to the warning about changing file names etc). Then when you import the .txt file you get the opportunity in the Text to Columns dialog boxes to nominate the delimiter and as you progress through the dialog boxes you can select the individual columns and nominate them as General, Text, Date etc. Note that when selecting the format for the date, you select the format that is already existing in the text file not necesarily the format that you use in your locality because you are telling Excel what the text data means. (I am in a dmy date format locality and if I get a text file in mdy format, then I select mdy format and then Excel displays it in dmy format in my worksheet.) One other thing. I have experienced Excel "Remembering" the previous txt file import details and importing the same as if it is a csv file without going through the Text to Columns dialog boxes. If you have this problem, close Excel and re-open it and start again. Hope this helps. Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy and paste row as a text within workbook including
Hi yet again Georgee,
Not sure how familiar you are with Windows Explorer options but I thought that I should include this so you are not delayed if you are not familiar with it. You will need to display the file extensions before you can change the filename extension from csv to txt. To do this:- Windows XP (and I think other versions prior to XP):- Open windows explorer and change to the required folder Select menu item Tools-Folder Options Select the View tab Under the Advanced settings heading, Uncheck Hide extensions for known file types. Windows Vista:- Open windows explorer and change to the required folder Select menu item Organize-Folder and Search options Select the View tab Under the Advanced settings heading, Uncheck Hide extensions for known file types. Also, I should have said before that you can simply select Text for all of the columns in the Text to Columns dialog boxes and they will display as text in the worksheet instead of other formats if that is what you are trying to achieve. Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select numbers for a formula from a cell including text? | Excel Discussion (Misc queries) | |||
Using If to combine answers including text and numbers | Excel Worksheet Functions | |||
Novice user trying to use variables including text and numbers | Excel Worksheet Functions | |||
copy/paste from excel to word including cell color | Excel Discussion (Misc queries) | |||
copy rows that contain certain text to a new sheet including the h | Excel Programming |