+ Reply to Thread
Results 1 to 4 of 4

Extract Only Number From A String

  1. #1
    Registered User
    Join Date
    01-18-2005
    Posts
    62

    Extract Only Number From A String

    Dear all,

    In Excel, is there any function that I can get number only in string that has number & text

    Ex: in cell A1 589as56
    I would like to get the string 58956 only

    Thank you very much

    Nam

  2. #2
    Norman Jones
    Guest

    Re: Extract Only Number From A String

    Hi Nam,

    Try:

    '=============>>
    Public Function DigitsOnly(sStr As String) As Variant
    Dim oRegExp As Object

    Set oRegExp = CreateObject("VBScript.RegExp")

    With oRegExp
    .IgnoreCase = True
    .Global = True
    oRegExp.Pattern = "\D"

    DigitsOnly = CLng(.Replace(sStr, vbNullString))
    End With
    End Function
    '<<=============

    A1: 589as56
    B1 = DigitsOnly(A1) ==> 58956


    ---
    Regards,
    Norman



    "lehainam" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Dear all,
    >
    > In Excel, is there any function that I can get number only in string
    > that has number & text
    >
    > Ex: in cell A1 589as56
    > I would like to get the string 58956 only
    >
    > Thank you very much
    >
    > Nam
    >
    >
    > --
    > lehainam
    > ------------------------------------------------------------------------
    > lehainam's Profile:
    > http://www.excelforum.com/member.php...o&userid=18615
    > View this thread: http://www.excelforum.com/showthread...hreadid=545735
    >




  3. #3
    excelent
    Guest

    RE: Extract Only Number From A String

    or try this 1 change 100 rows
    mark the first 1 and run macro
    numbers stay and rest disaper

    Sub koverter()

    Dim i, j, x, Indhold, tal

    For i = 1 To 100
    Indhold = ActiveCell.Value
    x = Len(Indhold)
    For j = 1 To x
    If IsNumeric(Mid(Indhold, j, 1)) Then tal = tal & Mid(Indhold, j, 1)
    Next
    ActiveCell.Offset(0, 0).Value = tal
    tal = ""
    ActiveCell.Offset(1, 0).Activate
    Next
    ActiveCell.End(xlUp).End(xlUp).Activate

    End Sub

    by the way, vhy cant i start a new question?
    i tryed click on New and select Question, but nothing happens?


  4. #4
    Ron Rosenfeld
    Guest

    Re: Extract Only Number From A String

    On Thu, 25 May 2006 23:44:26 -0500, lehainam
    <[email protected]> wrote:

    >
    >Dear all,
    >
    >In Excel, is there any function that I can get number only in string
    >that has number & text
    >
    >Ex: in cell A1 589as56
    >I would like to get the string 58956 only
    >
    >Thank you very much
    >
    >Nam


    You could download and install Longre's free morefunc.xll add-in from
    http://xcell05.free.fr/ (which will also give you acess to many other useful
    functions) and then use this Regular Expression formula (with your string in
    A1):

    =REGEX.SUBSTITUTE(A1,"\D")

    The formula substitutes <nothing> for everything in A1 that is not a digit.


    --ron

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1