1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: excell 16 digit number

Discussion in 'Business Applications' started by ksquared, May 18, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. ksquared

    ksquared Thread Starter

    Joined:
    Apr 19, 2004
    Messages:
    67
    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
     
  2. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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.
     
  3. ksquared

    ksquared Thread Starter

    Joined:
    Apr 19, 2004
    Messages:
    67
    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
     
  4. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,957
    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.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1053693