Extracting Numbers Out of String

[manav1103]

Hi Everyone,

I need help.

I want to extract only numbers from right out of a string

Examples of My string are :

1. Asia Pacific Billing ATT-14-015(eForm # 20282614)

2. Asia Pacific Billing ATT-14-018v2(eForm # 202938)

3. Asia Pacific Billing -eForm # 2024585

As you can see in some examples there are bracket and number count also varies from example to example.

Kindly help.

I just want the number shown in from of eform #

[zbor]

Here:

Please Login or Register  to view this content.

[JohnTopley]

Try

=SUBSTITUTE(MID(A1,FIND("#",A1)+1,255),")","")+0

[manav1103]

Thank You Everyone.

But if "#" is not present there, then it is showing error.

Kindly Help.

[manav1103]

Hi Everyone,

Please also consider below examples also so that one formula can be defined for all example.


1. Asia Pacific Billing ATT-14-015(eForm # 2028264) Australia
2. Asia Pacific Billing ATT-14-018v2(eForm # 202938) Sri Lanka
3. Asia Pacific Billing -eForm # 2024585 England

Thanking You.

[FlameRetired]

This is one way.

Please Login or Register  to view this content.

1. Asia Pacific Billing ATT-14-015(eForm # 2028264) Australia	2028264
2. Asia Pacific Billing ATT-14-018v2(eForm # 202938) Sri Lanka	202938
3. Asia Pacific Billing -eForm # 2024585 England	2024585

[José Augusto]

Hi
See if this helps you
Try
=AGGREGATE(14,6,VALUE(MID(MID(A1,AGGREGATE(14,6,FIND(" ",A1,ROW($A1:$A255)),1),999),1,ROW($A1:$A255))),1)

[JohnTopley]

I just want the number shown in from of eform #

BUT .....

But if "#" is not present there, then it is showing error

.... appears to conflict.

Dave has given you a solution But it still assumes a # is present.

[manav1103]

Yes Mr. John you are right, but I am talking of other case in which # is not present.

For Eg.

Asia Pacific Billing -eForm 2024585 England

Asia Pacific Billing ATT-14-018v2(eForm 202938) Sri Lanka

Kindly Help.

[FlameRetired]

Will eForm always be present?

Edit

If it is this would be another way.

Please Login or Register  to view this content.

[manav1103]

Thank You Dave,

But it is not necessary eform would be present, there can be random text den numbers.

[FlameRetired]

Can you post a comprehensive list of what you will encounter? It doesn't appear that we are getting a complete picture so far.

Maybe even a list of the countries in a lookup table?

[sandy666]

With UDF VBA regex (first read here) you can use simply function:

Please Login or Register  to view this content.

I assumed there is no less than 6 digits and no more than 8 digits in extracted number. If any number has more or less digits simply change values checked as red.
(based on your example)

[José Augusto]

Try this

Please Login or Register  to view this content.

[manav1103]

Hi Dave,

Please find attached sample.

[José Augusto]

Hi @manav1103
Have you try the formula I put in post #14?

[FlameRetired]

With a lookup table of countries in column E this formula in B1 and down.

Please Login or Register  to view this content.

[zbor]

this will extract last number but must have space before it:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[zbor]

Extend 255 if you have more than 255 characters in a cell

[ebyker]

Try the following custom function.

Calling cell would contain

Please Login or Register  to view this content.

where
S is the string containing the number
N=instance of number if there are more than one, +ve counting from the left and -ve counting from the right (default is 1 (first from left))
D = True if Decimals are to be recognised, False if Integer only to be returned; default id FALSE (integer only).

Good luck
B

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[zbor]

Ebyker,
Thank you for a solution.

Hower, please use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window.

[7absinth]

You dont need all above formulas. Write manually 2028264 on the first next cell, select on that cell and go to Fill>Flash Fill. Easy than any complicated formula or Macro!

[FlameRetired]

You dont need all above formulas. Write manually 2028264 on the first next cell, select on that cell and go to Fill>Flash Fill. Easy than any complicated formula or Macro!

Very good 7absinth, welcome to the forum and thank you . I've always done Flash fill by double-clicking the fill handle. I was never aware of this secondary(?) method. I must remember this trick. It worked.

You get rep on your first post.