Importing HTML data

lecxE · May 15th, 2002, 07:48 AM

I’m importing HTML data that includes 20 digit credit card numbers. Excel insists on converting a number like “11111111111234567890" to “1.11111E+19". Does anyone know of a way to tell Excel to treat the column containing the credit card numbers as text on import?

Thanks,

JC

PerfectTommy · May 15th, 2002, 07:55 AM

You should be able to format the cells you are imputing to as "Number". It looks like you have the format set to "General" now.

lecxE · May 15th, 2002, 08:02 AM

Thanks, I've tried that. When I format the cells as numbers with no decimal places, I get "11111111111234500000". Excel replaces the digits in positions 16-20 of my string with zeroes.

PerfectTommy · May 15th, 2002, 08:12 AM

If you do not need to perform calculations on the cell values (I can't think of why you would with credit card numbers), you can format as "Text". That should display the entire number. Sorry about the first suggestion. I thought that would have done it.

Vas · May 15th, 2002, 08:14 AM

Use "Text".
If all numbers are equally sized they will sort correctly. I can't imagine you need to do any mathematic operations on credit card numbers.

Vas · May 15th, 2002, 08:18 AM

Look at those answers!
Almost exactly the same!

Kinda spooky - isn't it?

PerfectTommy · May 15th, 2002, 08:24 AM

Great minds and all that, eh Vas? (Provided the suggestion works!)

lecxE · May 15th, 2002, 08:24 AM

When I format the numbers as text the data stays displayed as "1.11111E+19" instead of showing the credit card number, the only change is the data becomes left justified in the cell.

PerfectTommy · May 15th, 2002, 08:26 AM

I think we are on the right track, though. You should format the cells before you import the credit card numbers. Good luck!

cass108 · May 15th, 2002, 08:33 AM

You can custom format the data and keep it in numeric format. Just specify 20 # in the custom formatting box and the number should correctly display.

May 15th, 2002, 08:14 AM	#5
Vas Board Regular Join Date: May 2002 Location: Gothenburg, Sweden Posts: 74	Use "Text". If all numbers are equally sized they will sort correctly. I can't imagine you need to do any mathematic operations on credit card numbers. __________________ /Niklas Jansson

May 15th, 2002, 08:18 AM	#6
Vas Board Regular Join Date: May 2002 Location: Gothenburg, Sweden Posts: 74	Look at those answers! Almost exactly the same! Kinda spooky - isn't it? __________________ /Niklas Jansson

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 15th, 2002, 07:48 AM	#1
lecxE Board Regular Join Date: Apr 2002 Posts: 106	I’m importing HTML data that includes 20 digit credit card numbers. Excel insists on converting a number like “11111111111234567890" to “1.11111E+19". Does anyone know of a way to tell Excel to treat the column containing the credit card numbers as text on import? Thanks, JC

May 15th, 2002, 07:55 AM	#2
PerfectTommy New Member Join Date: Apr 2002 Posts: 15	You should be able to format the cells you are imputing to as "Number". It looks like you have the format set to "General" now.

May 15th, 2002, 08:02 AM	#3
lecxE Board Regular Join Date: Apr 2002 Posts: 106	Thanks, I've tried that. When I format the cells as numbers with no decimal places, I get "11111111111234500000". Excel replaces the digits in positions 16-20 of my string with zeroes.

May 15th, 2002, 08:12 AM	#4
PerfectTommy New Member Join Date: Apr 2002 Posts: 15	If you do not need to perform calculations on the cell values (I can't think of why you would with credit card numbers), you can format as "Text". That should display the entire number. Sorry about the first suggestion. I thought that would have done it.

May 15th, 2002, 08:24 AM	#7
PerfectTommy New Member Join Date: Apr 2002 Posts: 15	Great minds and all that, eh Vas? (Provided the suggestion works!)

May 15th, 2002, 08:26 AM	#9
PerfectTommy New Member Join Date: Apr 2002 Posts: 15	I think we are on the right track, though. You should format the cells before you import the credit card numbers. Good luck!

May 15th, 2002, 08:33 AM	#10
cass108 New Member Join Date: May 2002 Location: Ohio Posts: 29	You can custom format the data and keep it in numeric format. Just specify 20 # in the custom formatting box and the number should correctly display.