Separating same cell alpha & numeric data (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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.)?

Reply | Quote

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.)?

Reply | Quote

This custom function should work:

Steve

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

Reply | Quote

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.

Reply | Quote

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:

[indent]

---

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

---

[/indent]

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 and not type the whole thing)
Steve

Reply | Quote

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:

[indent]

---

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

---

[/indent]

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 and not type the whole thing)
Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

This custom function should work:

Steve

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

Reply | Quote

Use the ‘text to colums’ function and use the “#” as the delimiter.

Reply | Quote