Excel – How to trim out extra characters from values within an Excel column

I agree with Excellll; you're going to need VBA to convert the values in place to a string value.

This method won't result to text strings; but it might help you get the format you need.

Let's say these were my minutes:

1. Type 1440 into any blank cell. Select this cell and press Ctrl + C.

2. Select the range that contains the minutes.

3. Paste Special > Values > Divide

4. Click OK. Result:

   

5. Select the range of number again and press Ctrl + 1.

6. Go to Number > Custom

7. Type the format:

   [h] "h" mm "m"

Result:

If you'd like to change them back to minutes:

1. Type 1440 into any blank cell. Select this cell and press Ctrl + C.
2. Select the range that contains your hours/times.
3. Paste Special > Values > Multiply.
4. Press Ctrl + 1. Set the number format back to General.
5. You may get rid of the 1440 cell when you're done.

To get the number of hours in decimal form, use 60 instead of 1440. So for 330 (mins), you'll get 5.5 (hours).

I don't think Excel can do that. A possible work-around is to use Microsoft Word and snake the columns around: How to snake columns to use fewer pages

Using MS Word to Snake Columns (#msword)

This is probably the easiest to use for a one time usage, since I've not put in a dialog into my Excel macro described later.

1. Select and copy columns from Excel spreadsheet.
2. Paste into Word, this takes a few moments
3. Select the rows to be repeated at top in the table, then indicate this in Word with Table --> Rows to repeated
4. Select entire table using anchor, or selecting cell(s) in the table and then Table --> Select --> Table
5. Indicate number of rows using the columns button, or Format --> Columns if you want more control over placement

This example works well. The post offers other possibilities, too.