• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

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 Hermle
Andreas Hermle
2 Solutions
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)),"")

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))),"")
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
If DigitsFrom <> "" Then
    DigitsFrom = Evaluate(DigitsFrom) 'convert digits to number
    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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
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
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now