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
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Saqib Husain, SyedEngineerCommented:
=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
Ejgil HedegaardCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
SimonCommented:
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

Open in new window


and call it in the worksheet as in
=DigitsFrom(A2)
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Andreas HermleTeam leaderAuthor Commented:
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
Andreas HermleTeam leaderAuthor Commented:
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
Saqib Husain, SyedEngineerCommented:
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.