0

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?

2 Answers 2

4

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.

1
  • 1
    Advisable to remove days first, before day.
    – ADTC
    Sep 14, 2017 at 6:59
3

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.

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

You must log in to answer this question.

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