Tech Support Guy banner
Status
Not open for further replies.
1 - 9 of 9 Posts

·
Registered
Joined
·
285 Posts
Discussion Starter · #1 ·
When copying and pasteing a football team roster from the internet to an MS Excel spreadsheet, all goes well except for the player's height.
If a player is say 5' 11" tall, it comes up on the spreadsheet as May 11.
Is there a fix for this?
Thank you.
 

·
Registered
Joined
·
156 Posts
It could just be the formatting, if you select the colum and change the formatting to general what happens.

If nothing can you attach a copy with the names removed and i will be happy to have a look
 

·
Registered
Joined
·
7,837 Posts
Your current worksheet has the data permanently altered by Excel - it assumed that the heights were dates, and, since Excel tracks dates as how many days it has been since the beginning of last century (roughly), a height like 5' 8" was altered to 5/8/2009, which to Excel is represented by the number 39941.
There is no easy way to convert these back. I fiddled around and the two formulas,
=VALUE(E2) in column H (to find the number Excel has converted the heights to) and then
=IF(OR(RIGHT(H2,2)-RIGHT(H2,1)=40,RIGHT(H2,2)-RIGHT(H2,1)=30),"5 foot","6 foot")
finds the difference in the date ending numbers, and from that I can derive the heightin feet. But the height in inches is trickier.

Rather, I would suggest you copy your data and paste it first into Notepad.
Format your whole Excel spread sheet as Text, and then copy from Notepad and paste into Excel. That will retain the heights as text, not numbers, and you will not have this problem.
 

·
Registered
Joined
·
7,837 Posts

Attachments

·
Registered
Joined
·
156 Posts
Hiya

Sorry it has taken so long to get back to you.

There is a much simpler solution

Before you paste your selection into excel. Select column E and format it as text.

Then use Paste special (the little arrow under the paste button on the home tab) and select text. It worked for me
 

Attachments

1 - 9 of 9 Posts
Status
Not open for further replies.
Top