maintaining a 16 character number in a CSV file

[ksquared]

Hi,

Microsoft Office Excel 2007

Some of the cells in my Excel spreadsheet contain numbers that are 16 digits in length. In order to preserve the accuracy of the 16 digit number I have a double quote " in front of the digit string.

Excel Col A
"1234567812345678
"8765432187654321
"4567456745674567

I save the Excel as a CSV file and reformate Col A as text.
CSV Col A formatted as text
"1234567812345678
"8765432187654321
"4567456745674567

The CSV gets used by another program that won't except the ". Right now I manually remove the " from each cell (double click cell and delete the ") Is there a way to remove the " with a formula etc. Using the find (") and replace (with nothing) causes the data in the digit string to change.


CSV Col A after I manually removed the "
1234567812345678
8765432187654321
4567456745674567

Thanks in advance. K2

 

[c_m_s_jr]

Is there a reason you don't just format the value as text? Or do you pull the data and from somewhere else?

If you pull the data from somewhere else take the raw value it gives you and use the following formula to force it to text:

=TEXT(A1,"0000000000000000")

Where A1 houses the original 16 character number.

 

[Legacy 185660]

You can use 3 different formulas:

=SUBSTITUTE(A1,"""","")
=RIGHT(A1,16)
=MID(A1,2,LEN(A1))

 

[ksquared]

I can format it as Text while it's still in the excell sheet and I can also remove the ".

The problem I run into is that I do a clean-up on the CSV before it's exported into the next program, during the clean-up process the last 4 digits of the 16 digit number changes. The only way I found to keep the 16 d number accurate was to leave the " until I'm all done. I was hoping there was a way to remove the " in the CSV.

I added a colume (B) and tried all 3 formulas but all that displayed is the formula not the result. Col B was formated as text before I put the formula in. I changed the format to number and it worked.

Thank you very much for both responses