Extracting all numbers from a string

soccer4ward

New Member
Joined
Aug 11, 2020
Messages
6
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hello,

I am attempting to extract data from a string. I have employees copying tabular data out of another software. The software got updated, so now copied data only gets pasted as a string in one cell. I was hoping someone is able to assist with a VBA code or a formula to extract any numbers from that string. Numbers are 7 to 9 digits long and can be anywhere in the string (since copied data varies from employee to employee depending on how many columns they view in another software). For an example, if i have the following string:

Original string:
adkjasdf 123456789. adfa456 1234567 fdgja;gf 987654321 asdfja;df

Result:
123456789, 1234567, 987654321

As you can see I'm only interested in consecutive 7 to 9 digit numbers. Anything less or more should be ignored.

any help is greatly appreciated!

thank you,
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
1,121
Office Version
  1. 365
Platform
  1. Windows
You could try the second macro I posted
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

soccer4ward

New Member
Joined
Aug 11, 2020
Messages
6
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
I did try that as well. It works, but then it ignores needed numbers when text is right after them.
i.e. 123456789abc is not being caught because it's not a number, but number and text I'm guessing?

My logical thought (and i don't know if that's possible) is to create a section of the code to "ignore" specific instances that i can add as i come across them.
I'd like to add an "exceptions" section that i can edit on my own so i don't bug you too much. i,e:

>extract all 7 to 9 digit numbers
>unless they are in the following format:
>##########.###.#####
>###-###-#########
>start with a specific digit., etc.,

thanks so much!
 
Upvote 0

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
1,121
Office Version
  1. 365
Platform
  1. Windows
Ok, try this small change

VBA Code:
Function jec(cell As String) As String
 Dim it
 With CreateObject("VBScript.RegExp")
   .Global = True
   .Pattern = "\b\d{7,9}\b"
   If .test(cell) Then
     For Each it In .Execute(cell)
       jec = jec & IIf(jec = "", "", ", ") & it
     Next
   End If
 End With
End Function
 
Upvote 0

Forum statistics

Threads
1,186,330
Messages
5,957,251
Members
438,295
Latest member
nm005

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top