# Extract numbers from string in Column A and list them in Column B

Dear Experts:

I got more than 900 entries in Column A that look like this:

no stock
fully deliverable, 300 remain in stock
no stock
fully deliverable, 83 remain in stock
fully deliverable, 82 remain in stock
no stock
fully deliverable, 22 remain in stock
fully deliverable, 57 remain in stock
no stock
only partial delivery, 50 missing
fully deliverable, 57 remain in stock
only partial delivery, 23 missing
no stock
fully deliverable, 170 remain in stock
no stock
only partial delivery, 33 missing

I would like to extract the numbers in the cell entries in Column D (where existing) and ...
... list them in Column B using a formula.

Help is much appreciated.

Thank you very much in advance for your valuable and professional help.

Regards, Andreas
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
=IFERROR(LEFT(MID(A2,20,5),FIND(" ",A2,20)-20),"")

or to get a number

=IFERROR(VALUE(LEFT(MID(A2,20,5),FIND(" ",A2,20)-20)),"")

*********Edited*********
0
Commented:
The start point for the numbers are not always 20
Finding the comma first to get the staring point gives this formula
=IFERROR(VALUE(MID(A2,FIND(",",A2)+2,FIND(" ",A2,FIND(",",A2)+2)-(FIND(",",A2)+2))),"")
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Credit to Saqib for doing it in a formula. I think that only handles numbers up to 99999.

If you happened to want a function you could use this in a module
``````Public Function DigitsFrom(r As Range) As Variant

For x = 1 To Len(r)
c = Mid(r, x, 1)
cv = Asc(c) 'get the ASCII character code for the current character
If cv >= 48 And cv <= 57 Then '48-57 is the digits 0-9
DigitsFrom = DigitsFrom & c
End If
Next
If DigitsFrom <> "" Then
DigitsFrom = Evaluate(DigitsFrom) 'convert digits to number
Else
DigitsFrom = vbNullString 'return empty string if no digits found
End If
End Function
``````

and call it in the worksheet as in
=DigitsFrom(A2)
0
Thank you very much to all of you, I really appreciate the overwhelming support :-)
Will do some testing next morning. Till then and again thank you
0
Dear Simon and Ejgil,

I am really very impressed with your expertise. Works like a charm, very nice. Thank you very much for your great and professional help.

Saquib, also thank you very much for your great help, you have helped me a lot in the past, this time the others were better.

Regards, Andreas
0
EngineerCommented:
I definitely made a mistake in not realizing that the strings were not all the same length. You do not have to feel bad about that.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.