Is Excel text not really text?

Is Excel text not really text?

Post by Peter S. Kell » Mon, 27 Mar 2000 04:00:00



I am using a list of 9 character social security numbers in an excel
spreadsheet to retrieve information from an Oracle database. The Oracle
database requires that the social security numbers be text. But even
though I make sure the format of the SSNs in Excel is "text", Oracle
still rejects the SSNs. Only when I cut the SSNs out of excel, paste
them into a word file, save as ".txt", then paste back into the excel
spreadsheet does Oracle accept them. Does anyone have any idea what else
I can do to these SSNs so I don't have to do the cutting and pasting? Is
there another level of "text" within Excel that I can't find?

Thank you.
Pete Kelly


 
 
 

Is Excel text not really text?

Post by Jon Moe » Mon, 27 Mar 2000 04:00:00


Russ's way would work. Another way might be to have your formula or macro
place an apostrophe to the left of your numbers in the formula or macro that
requests the data. I don't use Excel very often but this definitely works in
Lotus. Formatting doesn't change the contents of a cell
Jon

> The social security number format in excel is a numeric format.  You can
> prove this by adding together two cells so formatted.
> You have to convert this to text by using something like this:

> =RIGHT("000000"&TEXT(A1,"#########"),9)


> >I am using a list of 9 character social security numbers in an excel
> >spreadsheet to retrieve information from an Oracle database. The Oracle
> >database requires that the social security numbers be text. But even
> >though I make sure the format of the SSNs in Excel is "text", Oracle
> >still rejects the SSNs. Only when I cut the SSNs out of excel, paste
> >them into a word file, save as ".txt", then paste back into the excel
> >spreadsheet does Oracle accept them. Does anyone have any idea what else
> >I can do to these SSNs so I don't have to do the cutting and pasting? Is
> >there another level of "text" within Excel that I can't find?

> >Thank you.
> >Pete Kelly




 
 
 

Is Excel text not really text?

Post by JDDLE » Mon, 27 Mar 2000 04:00:00


Russ,

I don't understand how you got the ssn's to add (or subtract).  Using E 97
SR2, I tried it with my own ssn using General, Numeric, and Special/ SSN and
got a #Value! each time.

Dominic

 
 
 

Is Excel text not really text?

Post by RUSS BARTOL » Tue, 28 Mar 2000 04:00:00


The social security number format in excel is a numeric format.  You can
prove this by adding together two cells so formatted.
You have to convert this to text by using something like this:

=RIGHT("000000"&TEXT(A1,"#########"),9)


>I am using a list of 9 character social security numbers in an excel
>spreadsheet to retrieve information from an Oracle database. The Oracle
>database requires that the social security numbers be text. But even
>though I make sure the format of the SSNs in Excel is "text", Oracle
>still rejects the SSNs. Only when I cut the SSNs out of excel, paste
>them into a word file, save as ".txt", then paste back into the excel
>spreadsheet does Oracle accept them. Does anyone have any idea what else
>I can do to these SSNs so I don't have to do the cutting and pasting? Is
>there another level of "text" within Excel that I can't find?

>Thank you.
>Pete Kelly



 
 
 

Is Excel text not really text?

Post by Harlan Grov » Tue, 28 Mar 2000 04:00:00




> Russ,

>I don't understand how you got the ssn's to add (or subtract). Using
>E 97 SR2, I tried it with my own ssn using General, Numeric, and
>Special/ SSN and got a #Value! each time.

Lemme guess, when you entered your SSN, you typed in the hyphens as
well? If so, you typed in text that's appearing as-is. Type in only the
digits, and it's a number you can add, subtract, multiply, divide, . . .

Sent via Deja.com http://www.deja.com/
Before you buy.