Normalizing height data entered into excel columns

I’m pasting some data into Excel columns and the data is in the format 6-5 for example, which represents a height.

How can I make it so when the data is entered Excel automatically turns it into 6'5"?

I’m assuming that the cell that you pasted the data in is formatted as Text (otherwise Excel would interpret “6-5” as a Date). If cell A1 contains 6-5 as text, you can enter the following formula in another cell to return 6' 5" as text:

=SUBSTITUTE(A1,"-","'")&""""

The formula changes the - to ' and then appends ". Note that in a formula, you must enclose text in double quotes, and if your text includes a double quote, you need to change the double quote to two double quotes, so """" in the formula just represents a single double quote.

Edit to address OP’s comments

If you are pasting data into columns A through D, and the height is in column B, you can put the formula in column E (or some other column not overwritten by the paste. Now you can either:

1. Copy the cells with the formula and use Paste Special | Values to overwrite the heights in column B. Or…
2. Hide Column B (don’t delete it) and you will still have the formatted heights in column E (or wherever you put the formula). Or…
3. Use Group from the Data ribbon to mark column B as a subsidiary column.You can then quickly hide and unhide the column using the “+” or “-” symbols above that column.

I prefer the 3rd method.