[ksquared]

I store 16 digit numbers in my spreadsheet cells.

Example: 1234-1234-1234-1234

I need to export the data to another program using a csv file. The program I’m exporting to will not accept dashes, spaces or double quotes “.

If I format the cells that contain the 16 digit numbers as “text” and manually remove the dashes, double quotes, spaces etc., the number will stay intact 1234123412341234

However, if I use the find and replace function it turns into this: 1234123412341230
The last number turns into a zero.

I’m familier with the issue excel has with number over 15 postiitons in length and have worked aournd this by changing the format to text.

I’ve used the find and replace function for years on cells that have a text format without any problems. I even have a find/ replace in a macro which is still functioning. I don’t understand why something that has always worked has stopped working. Can someone help me out please. Thanks in adance.

Running XP and Microsoft Office 2007

[The Villan]

Excel is treating it as a number whatever you do. It matters not to Excel that you have formatted it to text. As soon as you remove the -, it treats it as a number.

You need to get an ' in front of the data in each cell, before you do the search and replace.

A macro should sort it. I still now how to do simple macro's but have forgotten how to loop the macro to insert the ' in front of each record.

Maybe you know how to do that. If not post on here agin to ask for help creating the macro and the Guru's will help.

Soembody may even have a simpler answer to this.

[ksquared]

Thanks. It seems to work just fine when the cell is defined as "text" and I remove the dashes, spaces and double quotes manually. It's the find/replace function that doesn't work.

I'm using the find/repelace in a macro on another spreedsheet without any problems. But since this is a new spreedsheet, I need to redo everything. I added a single quote to the beginning of each number and the find/replace is working. I didn't bother removing it when I imported form the other program. . It kept the 16 digits intact and the program I'm importing into didn't seem so mind (it ignored the single quote).

It's still odd that I can edit the contents manually but not when using the find/replace. I would have thought the find/replace was doing the same thing . Thanks for your response though. Appreciated

[Keebellah]

If you are Finding and replacing and searching for the value in the whole cell the ' throws it off, if you do the find and replace for part than it should work but then the problem will arise that if part of the number coincides with another cell it will change too.