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?

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
    Advisable to remove days first, before day. – ADTC Sep 14 '17 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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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