Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Separating same cell alpha & numeric data (Excel 97)

    Hello!
    Does anyone have any ideas on how I might separate alpha & numeric data that is in one cell into two separate cells, having the alpha in one cell and the numeric dump to a separate cell, or deleting the numeric data all together. An example.... Target Stores all have there own Store number, and on our purchasing card data the vender shows up as Target #1234, then another purchase at a different Target Store would show up as Target #5678. I really only want to see all the Target's as a whole. Target is only one example of a large database of vendors with their unique store numbers, and all the Vendor names are different lengths, and the store numbers can show up in any position in the cell. I'd like to delete all numeric items to see if my pivot table would then summarize all the like vendors. Any ideas would be appreciated.
    Thanks!
    LJM

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating same cell alpha & numeric data (Excel 97)

    If the alpha and numeric are always separated by a #, then you can use =left(a1,search("#",a1)-1) to get the alpha portion. If it's not so conveniently separated we'll have to do something more complicated. What if the name of your vendor has a number in it (e.g. 7-11, 1StopShop, etc.)?

  3. #3
    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: Separating same cell alpha & numeric data (Excel 97)

    This custom function should work:

    Steve
    <pre>Option Explicit
    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</pre>


  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating same cell alpha & numeric data (Excel 97)

    Steve,
    A question about your solution. Why set up the wf object? When I do something similar, I just use application.substitute(blah blah blah.

  5. #5
    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: Separating same cell alpha & numeric data (Excel 97)

    I have always used it this way to use the worksheetfunctions in xl that VB can use. I have never done it with just application.
    The woksheetfunction is a new object added to the application object to hold these functions according to the help in XL97:

    <hr>In previous versions of Microsoft Excel, worksheet functions were contained by the Application object.<hr>

    So it seems that it "could be" used without it in earlier verions, but has the backward compatibility been maintained to drop it in the later versiosn of XL?

    One advantage to using it is the "bonus prompting" when entering it (you can type the first few charc of the function and then hit <tab> and not type the whole thing)
    Steve

  6. #6
    New Lounger
    Join Date
    Oct 2003
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating same cell alpha & numeric data (Excel 97)

    Use the 'text to colums' function and use the "#" as the delimiter.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Separating same cell alpha & numeric data (Excel 97)

    Thanks so much for the awesome function... it worked perfectly! I wish I knew how to do VBA!!!
    LJM

  8. #8
    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: Separating same cell alpha & numeric data (Excel 97)

    You are welcome.

    About learning VB - Plenty of ways to learn:
    Take a course
    Get a good book
    Study the code that people post
    Use the macro recorder to do things and study the code.

    Steve

Posting Permissions

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