Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Finding The First Number In A String

1. I agreed to these rules
Join Date
8th April 2007
Posts
3

## Finding The First Number In A String

Hello all!

I am a major noob to all of this and I hope this question isn't one that has been asked multiple times already. I searched through these forums, but was unable to find an answer.

We are trying to make either a macro or a function that will look through the contents of a cell, find the first NUMBER and then paste the results to another column. Below is an example of what may be in cell A1:

#BC7K,03/30/2007,0.00636,0.0069,0.00614,0.0062,0.0,0

We want only to find the FIRST NUMBER in this string, so the result should be 7. Any help you could give us on this would be greatly appreciated! Thanks in advance!
Last edited by Brendenguy; April 8th, 2007 at 09:56.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Finding The First Number In A String

Try this, using the ISNUMERIC function in VBA

VB:
```Sub FindNumeric()

Dim str As String
Dim i As Integer

str = Cells(1, 1).Value

For i = 1 To Len(str)

If IsNumeric(Mid(str, i, 1)) Then

Debug.Print Mid(str, i, 1)

End If

Next i

End Sub

```

3. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## Re: Finding The First Number In A String

#BC7K,03/30/2007,0.00636,0.0069,0.00614,0.0062,0.0,0

UDF
use in cell like

=Brenden(A1) as text output
=Brenden(A1)*1 as number output
VB:
```Function Brenden(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "\d"
Brenden = .execute(txt)(0)
End With
End Function

```

Excel Video Tutorials / Excel Dashboards Reports

4. Senior Member
Join Date
5th April 2007
Location
Fort Collins Colorado
Posts
103

## Re: Finding The First Number In A String

Macro is the way to go and there are a couple of good examples posted here for you.

If you don't want to tackle a macro for any reason, the following highly inelegant (LOL) Excel formula will evaluate the first 5 characters for a numeric value and return the first one it finds. It retuns "nope" if no number within 5 characters.

You would neeed to replace "C6" with the cell you wish to evaluate.

=IF(ISNUMBER(VALUE((MID(C6,1,1)))),VALUE((MID(C6,1,1))),IF(ISNUMBER(VALUE((MID(C6,2,1)))),VALUE((MID(C6,2,1))),IF(ISNUMBER(VALUE((MID(C6,3,1)))),VALUE((MID(C6,3,1))),IF(ISNUMBER(VALUE((MID(C6,4,1)))),VALUE((MID(C6,4,1))),IF(ISNUMBER(VALUE((MID(C6,5,1)))),VALUE((MID(C6,5,1))),"nope")))))
Last edited by ByTheCringe2; April 8th, 2007 at 14:16.

Excel Video Tutorials / Excel Dashboards Reports

5. Super M‌oderator
Join Date
4th July 2004
Location
Canada
Posts
2,371

## Re: Finding The First Number In A String

Try...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),1)

Hope this helps!

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Finding The First Number In A String

Todkerr, Please don't put code tags round formulas, only round VBA code.

Excel Video Tutorials / Excel Dashboards Reports

7. I agreed to these rules
Join Date
21st February 2013
Posts
1

## Re: Finding The First Number In A String

Originally Posted by Domenic
Try...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),1)

Hope this helps!
This worked perfectly for an application I have as well - but why do you need A1&"0123456789"? I can't figure out what it's doing (though it doesn't work without it).

Excel Video Tutorials / Excel Dashboards Reports

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno