Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.