Separate text from numbers

you could extract the numbers see attached will do 3 numbers

If you want a simple formula and the ability to extract as many individual numbers as there may be, you could add a custom function to your worksheet. It would evaluate the entire text string and pull out all numeric values.

If your text string were in A1, then this formula in B1:

=NUMS(A1)
..would result in 4089, 4111

Or:
=NUMS(A1, "-")
...would result in 4089-4111 by using the optional second parameter to use a custom delimiter.

Here's the UDF I have for this:

Please Login or Register  to view this content.

I highlighted in red the only part of the code you might need to edit, it's the characters to search for in the string and replace with spaces... so far just a dash, comma and ampersand.

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

@JB

this was very helful and detailed..thanks

however i am getteing value "none" in B if the number and text in A are joined
private4089,trailer4111

can this be fixed ?
many thanks

Please don't quote previous posts for no reason, it just clutters the thread making it hard to read. Use the QUICK REPLY box below.

Why are you changing your text string syntax now? The first was simple and usable. Are you changing it now for some good reason? If not, don't join the text and numerics that way. The UDF works and merging the text that way means the whole thing would need to be redone in a far less efficient way.

legolas, given it is your intention to split the numbers I confess I'm not sure why you chose to ignore Martin's example ?
The functions provided would extract the numbers to B & C automatically - for either syntax.

If Martin and I have taken care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

how many are you likely to need?
here is a (not very efficient udf )that will extract all numbers into one cell .then copy paste special values then split with text to columns
use as =remove_text(a1) and drag down

Please Login or Register  to view this content.

FWIW - if you opted for UDF - Regular Expressions may be worth considering...

Please Login or Register  to view this content.

Assume string in A1, numbers to go in B1, C1 etc...

Please Login or Register  to view this content.

The second variable is optional - ie should you only be looking to return a single numeric value (first) it could be omitted - in this case we increment the value to return 1st, 2nd, 3rd etc...

Not very effiicient though doing this with functions though IMO...a sub routine would be better.

EDIT:

if you want the output for finds to be numeric adjust the 2nd NumberExtract line to:

Please Login or Register  to view this content.