I would like to extract a number from a sentence in excel to a separate column using a formula

Question

I have an excel spreadsheet connected to an MS form and the MS form has options to choose for each question which pulls through into an excel table for example: -

1 - Doesn't understand Excel

Each answer always starts with either 1, 2, 3 or 4 followed by text as shown in the example above. Using a formula I would like to extract the number, in this example the "1" to a column to the right of the text.

I have tried using Right, Left and lookup but everytime it returns VALUE.

Thank you!!

Answer 1

Answered here:

https://learn.microsoft.com/en-us/answers/questions/2154616/using-iferror-with-left-formula-to-separate-number

Answer 2

We would be able to five you much better advice if you showed us what you tried.

If the number is always at the start of the string and followed by a blank, then the following formula will extract the digits and convert the resulting text to a numeric value:

=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))

xx

Answer 3

I have managed to find the solution!

I used =LEFT(A2,1) which brings back the number I need, however now for those that don't have numbers it is bringing back the first letter which I don't want.

For example, when a question hasn't been answered the response that appears on Excel is "Absent" so this formula is brining back "A".

Is there a way to correct the formula so if there is not a number present it just shows blank?

I tried =IFERROR(LEFT(A2,1,),"") but this still brings back the "A".

Thank you!