How do I display heights (6' 2") in Excel columns?

[Chopdawg]

I'm trying to copy-and-paste a football roster into Excel. The roster lists
the heights & weights of the players. When I paste into Excel, heights (ex
6' 2") do not transfer; instead, only a list of numbers appears. I've tried
several different formats for cell numbers with no luck. Can anyone help?
Thanks!

 

[JMB]

If the data is actually numbers, you could try a custom format:
#' 00''

(note the last characters are two apostrophes, not a quotation mark)

If text (maybe your data has a space in it), you could try
=TEXT(TRIM(A1),"#' 00''")
(last 3 characters are two apostrophes followed by a quote)

There is an assumption that your data does not have extra characters that
look like spaces (you could do a search/replace for spaces and/or the
character generated by Alt-0160) and will be 3 numbers, not mixed such as
5 10
5 4

If it is mixed, you could try
=LEFT(A1)&"' "&IF(LEN(TRIM(A1))>1, TEXT(RIGHT(TRIM(A1),LEN(TRIM(A1))-1),
"00"), "00")&"''"