Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: excell 16 digit number


(!)

ksquared's Avatar
ksquared ksquared is offline
Member with 67 posts.
THREAD STARTER
 
Join Date: Apr 2004
18-May-2012, 01:21 AM #1
Solved: excell 16 digit number
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

Last edited by ksquared; 18-May-2012 at 01:34 AM..
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
19-May-2012, 09:51 AM #2
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's Avatar
ksquared ksquared is offline
Member with 67 posts.
THREAD STARTER
 
Join Date: Apr 2004
19-May-2012, 06:37 PM #3
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's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,385 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
23-May-2012, 04:56 AM #4
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

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑