Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In order to treat them as numbers?

I copied the column from another program which has a list of durations. Unfortunately Excel will not treat these cells as numbers at all so I can't use them in formulas.

I've tried using the Format Cells tool and selecting "Number", but this does not change the cell at all - it still displays "1 day" and formulas referencing it still do not work.

Is there a simple way where a cell value starts with a number and is appended by a string to just get the number value?

share|improve this question
up vote 4 down vote accepted

Select the column that contains the data.

Press CTRL+H to load the find and replace dialogue.

For Find, enter day (note the space before the word day) For Replace, leave empty. Click on the Replace All button.

This will remove all occurrences of the word ' day' leaving you with your number.

share|improve this answer

Try this formula:

=IFERROR(VALUE(LEFT(A1,SEARCH(" ",A1)-1)),A1)

This will try the get the number before any space and convert it into a number.

share|improve this answer
    
Peter, it seems that for SU this is too complicated) Your answer fits SO nicely, my +1. – Ksenia Feb 20 '13 at 17:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .