other than number

Colin Legg · #2 02-17-2012, 08:06 AM

Since there could be many non-numeric characters, the easiest option is to write a VBA UDF which will parse the numbers from the start of each string for you. This has been done lots of times before, so I did a quick search and found this function written by Harlan Grove:

Code:

Function DigitsFirstID(s As String) As String
  'Harlan Grove, worksheet.functions, 2003-10-20
  'extract first string of  digits,  based on
  '-- http://google.com/groups?threadm=_RK...newsranger.com
    Dim i As Long, j As Long, n As Long
    n = Len(s)
    i = 1
    Do While i <= n And Mid(s, i, 1) Like "[!0-9]"
        i = i + 1
    Loop
    j = i + 1
    Do While j <= n And Mid(s, j, 1) Like "[0-9]"
        j = j + 1
    Loop
    DigitsFirstID = Mid(s, i, j - i)
End Function

This needs to be copied and pasted into a standard code module in your workbook, and then you can use a formula such as this to extract the number:

=DigitsFirstID(A2)

I realise that VBA may be new to you so I have attached an example.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
number to text	acki	Excel	1	01-26-2012 07:47 AM
convert to number	gsrikanth	Excel	1	01-09-2012 01:01 AM
number change 11 to 12	uoume	Office	0	10-30-2011 06:33 PM
Mailing: how to make the "page number" in Word is the same as "row number" in excel w	Jamal NUMAN	Word	1	09-03-2011 11:37 AM
Last Number in a Column	paulrm906	Excel	1	12-05-2008 07:51 PM