Remove Numbers and Characters in a String

[langhamc]

I have Column A that has a string with a mix of names, numbers and other characters. I would like to remove the numbers from the string, the "#" and the last ";". There are one or could be several names in column A. Example follows:

Column A
Row 1 Smith, Stan;#1
Row 2 Smith, Stan;#1;#Smith, Fancine;#25;#Smith, Steve;#101
Row 3 Smith, Haley;#50;#Smith, Steve;#101


Want column B to look like this:

Column B
Row 1 Smith, Stan
Row 2 Smith, Stan;Smith, Fancine;Smith, Steve
Row 3 Smith, Haley;Smith, Steve

Thanks!

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use in Column B to return your desired output...

Function CleanedData(S As String) As String
  Dim X As Long
  CleanedData = S
  For X = 1 To Len(CleanedData)
    If Mid(CleanedData, X, 1) Like "[0-9#]" Then Mid(CleanedData, X) = Chr$(1)
  Next
  CleanedData = Replace(CleanedData, Chr$(1), "")
  Do While CleanedData Like "*;"
    CleanedData = Left(CleanedData, Len(CleanedData) - 1)
  Loop
End Function

To install this UDF, press ALT+F11 from any worksheet to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that opened up. That's it... you are done. Go back to your worksheet and put this formula in cell B1 and copy it down as needed...

=CleanedData(A1)

*** EDIT ***
I just noticed you wanted doubled up semi-colons collapsed down to single semi-colons. This code will do that and everything the above code did too...

Function CleanedData(S As String) As String
  Dim X As Long
  CleanedData = S
  For X = 1 To Len(CleanedData)
    If Mid(CleanedData, X, 1) Like "[0-9#]" Then Mid(CleanedData, X) = Chr$(1)
  Next
  CleanedData = Replace(CleanedData, Chr$(1), "")
  Do While CleanedData Like "*;"
    CleanedData = Left(CleanedData, Len(CleanedData) - 1)
  Loop
  Do While InStr(CleanedData, ";;")
    CleanedData = Replace(CleanedData, ";;", ";")
  Loop
End Function

 

[langhamc]

It worked! You are the greatest! Thanks!