Removing apostrophe from text string

[The_Grudge]

I have a program that queries a database and loads data into a DataGridView. From there, I have a button that copies the datagrid data onto the clipboard where I can paste it into an Excel spreadsheet.

The issue I'm having is with my date fields. Here's what's going on. I have a data reader and as I loop through that I put the fields into a dataset. So, for the birthdate I have this:

  anyRow("DOB") = "'" & Format(ERDataReader.Item("Birthdate"), "MMddyyyy").ToString

This works as I would expect and places a date in the datagridview like "02031927". The problem is that it for some reason puts: '02031927. Note the apostrophe in front.

I want the date exactly like it is, just without the apostrophe. How do I accomplish this?

In a similar vein, I have a field called "Visit Type" which is supposed to be 3 characters long (e.g. 002). I format this field accordingly and it looks perfect in the DataGridView:

 anyRow("LOS") = Format(DataReader.Item("LOS"), "000").ToString.PadLeft(3, "0")

If I copy the datagridview contents to the clipboard and paste into Excel, a value like 003 is converted to just 3. How do I maintain the leading zeros?

[techgnome]

Excel needs the apostrophe... otherwise it will treat it as a number... which you don't want (you'll lose the leadong zero if you do) ... since you're setting a string with a numeric value, in order to preserve the value, Excel puts an apostrophe on the front of it. That's also why you get 3 instead of 003 ...

-tg

[dolot]

Interesting... did not know that.

[techgnome]

try it... open excel, select any cell... enter 003 ... in the next cell enter '003 ... and look at the difference.

-tg

[dolot]

Tried some different cell formats... Excel really doesn't like numbers stored as text. Seems to always squawk about it.

[techgnome]

You sound surprised. Of course it doesn't. It's a number crunching spreadsheet system... it was meant to do calculations and deal with numbers.

-tg

[dolot]

It's not that surprising. It's just that I've seen so many folks use excel for things other than numbers, or for a mix of text and numbers.

[techgnome]

Yeah, like a lot of office applications it often gets misused.

-tg