Solved: Find all the numbers in a document

[ALe]

Hi everybody,

is there a way to find all the numbers (one by one) of a document?

[TonyJollans]

Depends exactly what you want but the simplest way is Find and Replace - enter ^# in the Find what box and press Find Next.

[mvidas]

Hi ALe,

You could also use a VBA macro using regular expressions. Give this a try:Sub FindAllNumbers()
Dim docText As String, curSel As Range
Dim RegEx As Object, RegC As Object, RegM As Object
'load document text into docText variable
Set curSel = Selection.Range
Selection.WholeStory
docText = Selection.Text
curSel.Select

Set RegEx = CreateObject("vbscript.regexp")
With RegEx
.Global = True
.MultiLine = True
.Pattern = "-?\d+(\.\d{0,2})?" '"\d+" '"\d"
End With
If RegEx.Test(docText) Then
Set RegC = RegEx.Execute(docText)
For Each RegM In RegC
MsgBox RegM
Next
End If

Set RegEx = Nothing
Set RegC = Nothing
Set RegM = Nothing
Set curSel = Nothing
End SubThe pattern can be changed easily (likely to the "\d+" commented out) if it doesnt pull what you're looking for
Matt

[fumei]

Nice one Matt. Just one comment...could you please use the underscore character in your code? Or, in this case, separate lines for your Dim statements. It makes it easier for those of us without huge monitors and high screen resolution - the VBA window is smaller. It is a real pain scrolling left/right as well as up/down.

Thanks!

[mvidas]

Sure thing, I can break them into separate lines.
I actually only use a one-space indentation in my VBE, which when converted to a four-space indentation makes it a bit tougher to read too.
For whatever reason I have an aversion to using less lines, but I can easily break them out when writing for other people :)

[fumei]

Well you certainly do not have to use a separate line for each, really. The change you made is fine. Two or three declarations works well.

I appreciate it!

[ALe]

WOW!

Thank you very much. It's the first time I use RegEx and I find it great. Could you tell me where to find a guide for all the characters like "\d+" that can be used for all kind of search?

[mvidas]

Sure! Take a look at Dave/brettdj's kb entry, it is how I first learned to use it, and is a great resource:
http://vbaexpress.com/kb/getarticle.php?kb_id=68

Feel free to ask as well if you have any questions about it!

[ALe]

Great I'll have a look!

[ALe]

The word guide inside the excel file it is exactly what I was looking for. Thank you very much!

[mvidas]

I keep that reference table next to my computer, and refer to it frequently. Great information there
Again, let us know if you need help with regexp, can be really powerful stuff

[ALe]

I found the guide very clear and I'm using it. It's great. thank you. Up to now I haven't had any problem with regexp, but in case I'll post a new thread.

I'm going to mark this thread as solved
:thumb