How to copy and paste row as a text within workbook including numbers

[GorKo]

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

 

[OssieMac]

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]

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

Interesting, I will check it when I am back in my lab, thanks for
reply.

G

 

[GorKo]

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

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

 

[OssieMac]

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

 

[OssieMac]

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