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

Question

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

Emma Leigh 20

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!!

3 answers

Your answer

Answer 1

riny 665 Volunteer Moderator

Answered here:

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

Answer 2

Barry Schwarz 3,746

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!

Share via

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

3 answers

Your answer