Forum Discussion
Formatting Feet and Inches in one cell
Hi all,
I am working with a spreadsheet of archived baseball team rosters (each year's roster is contained as one sheet in the larger workbook). These were previously on paper, but are now being digitized.
The problem we are running into is with player heights (i.e. feet and inches). As you can see in the example screenshot below, heights have been typed in feet-inches format; for example the first player has a height of 5 feet, 11 inches.
However, Excel is formatting these as dates rather than heights (i.e. the first player is showing May 11 rather than simply 5-11). Although the values read correctly in this spreadsheet, it presents an issue when we ingest this data into our website, which is expecting heights in #-# format with feet and inches. Instead, for the first player the website is receiving 5/11/2023 and so on. Because of this the website disregards the height column for most players. The exception is anyone whose height doesn't conflict with a date, for example 6 feet even (6-0).
If I change the column category to either General or Text, the values change to a random string of numbers.
Given that we have about 40 seasons worth of player rosters in this workbook, is there an efficient process that we could take this data and put it into a format suitable for importing (such as a simple 5-11, 6-3, etc)?
Thanks in advance for any insight, and happy to share additional context/details if needed.
5 Replies
- Thanks all, for your replies and insight. Some follow up info:
1. Thank you Riny for the tip on column formatting. I forgot about the apostrophe trick!
2. These paper rosters for a college baseball team (which go back as far as the 1970s) were typed by hand into a cloud-based Google spreadsheet, which I then downloaded as an XLS file. For a couple reasons we preferred hand-typing rather than an automated process.
3. Here is an example of how the rosters are presented once imported/ingested. Different school, but same website platform and CMS:
https://bryantbulldogs.com/sports/baseball/roster
Intent here by typing the paper rosters then importing, is to display historical team rosters (i.e. 1985) in the same way as the roster for 2024 team. With the exception of anything that might not be available on a year-to-year basis--for example, the paper version of our 1979 roster does not have jersey numbers.
4. XLS is preferred for importing format, but CSV also can work
After reading these replies I also went into the original Google spreadsheet and changed the height column formatting to plain text. The heights *appear* to be kept in their originally-typed format (such as 5-11). I will try importing a few rosters with this adjustment.
Thanks again everyone!RyanGallant Well, I don't work with Google sheets, but if you can provide a link hat give access to the to a file, than it shouldn't be too difficult to transform it to an Excel file and, in turn, transform it to something you can use to upload to the web-site. Not sure though without seeing an example of a file.
- when we ingest this data into our website,
What kind of website?
ingest?
upload .xlsx or .csv file? How are you planning on using that height data? If it's purely a data point for sharing (e.g., in a printed roster), then the store-as-text solution Riny_van_Eekelen has proposed is right on target.
If you plan ever to use the data for any sorting or ranking on the basis of height, or calculation of average height of a team, etc., you might want to consider either
- converting the feet to inches and posting height in inches (5' 11" becomes 71"), OR
- using two columns, one for feet, one for inches)
Personally, I'd go for the latter, because that could always be used in a roster listing AND it would serve well as the basis for sorting, averaging, etc.
RyanGallant How exactly are you digitising the 'paper' data? Are you typing in everything be hand or is it automated in some way?
If by hand, format the height column to TEXT before you start entering the data. Then Excel will not transform 5-11 to a date. Alternatively, precede the height with an apostrophe. Thus, enter '5-11 .
