**QUESTION :**

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"`

?

**ANSWER :**

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:

- Copy the cells with the formula and use Paste Special | Values to overwrite the heights in column B.
**Or…** - 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…** - 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.