Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Seperating numbers and letters in a cell (2000)

    I am trying to do a text to columns without a delimiter. The only difference between the data that is listed is that there are letters then numbers. I am trying to create two columns, one containing the letters, and one containing the numbers. So far, I have been able to create a column with only the letters using the following function, which I found in a previous posting.

    Function NoNumbers(sWord As String) As String
    Dim wf As WorksheetFunction
    Dim x As Integer
    Set wf = Application.WorksheetFunction
    For x = 0 To 9
    sWord = wf.Substitute(sWord, Format(x, "0"), "")
    Next
    NoNumbers = sWord
    End Function

    I am trying to create a function that will only show the numbers.

    Please help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Seperating numbers and letters in a cell (2000)

    How about this? (It will give a #NUM error if the string has no numbers)

    Steve

    <pre>Option Explicit
    Function JustNumbers(sWord As String)
    Dim x As Integer
    Dim sTemp As String
    Dim sNumbers As String
    sNumbers = ""
    For x = 1 To Len(sWord)
    sTemp = Mid(sWord, x, 1)
    If Asc(sTemp) >= 48 And _
    Asc(sTemp) <= 57 Then _
    sNumbers = sNumbers & sTemp
    Next
    If sNumbers = "" Then
    JustNumbers = CVErr(xlErrNum)
    Else
    JustNumbers = Val(sNumbers)
    End If
    End Function</pre>


  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating numbers and letters in a cell (2000)

    That works great. Thanks!

Posting Permissions

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