Extract all Dashes and Upper Case letters

[aarbogust]

I have a cells that contain Names and Dashes ex: "Aaron Arbogust -- John SMith -- Bret Johnson -- Dan Bologna"

I would like that to extract all Upper case letters and Dashes to read:"AA--JSM--BJ--DB"

Basically extract Uppers and Dashes OR remove Lowercase letters and spaces

 

[tonyyy]

aarbogust,

Welcome to the Board.

Assuming your Names and Dashes are in Column A, the following will put the results in Column B...

Sub UCaseAlphaOnly()
Dim r As Range, r2 As Range
Dim i As Long
Application.ScreenUpdating = False
For Each r In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    Set r2 = Cells(r.Row, 2)
    r2.Value = r.Value
    For i = Len(r2.Value) To 1 Step -1
        If Not Mid(r2.Value, i, 1) Like "[A-Z]" And Mid(r2.Value, i, 1) <> "-" Then _
            r2.Value = Replace(r2.Value, Mid(r2.Value, i, 1), "")
    Next i
    r2.Value = Trim(Replace(r2.Value, "  ", " "))
Next r
Application.ScreenUpdating = True
End Sub

Cheers,

tonyyy

 

[Eric W]

Welcome to the board.

If you have one of the new versions of Excel that has the CONCAT function, then this would work:

=CONCAT(IF(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ-")),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))
confirmed with Control+Shift+Enter.

If you don't, then you'll probably need some version of VBA to do it, such as tonyyy's macro, or a UDF.

 

[Roxxien]

Hello and welcome in the forum.

I created a function just for you.

Function ExtractCharacter(Data)
Dim i As Integer
For i = 1 To Len(Data)
    If (Mid(Data, i, 1) = UCase(Mid(Data, i, 1))) And (Mid(Data, i, 1) <> " ") Then
        ExtractCharacter = ExtractCharacter & Mid(Data, i, 1)
    End If
Next
End Function

How to use that function?
1. Open the VB editor with alt+f11
2. Insert a new module with a right click in the left menu
3. Copy the above code and copy it in the new module
4. Close the VB editor
5. Use your new function like any other base Excel function
6. Save as your Excel files a macro enabled version (.xlsm)

 

[JoeMo]

You could also use a user-defined function (UDF) like a worksheet function. So, if your names and dashes are in cell A1, in cell B1 you could enter:
=UppersAndDashes(A1)
See instructions for installing the UDF below the function.

Function UppersAndDashes(S As String) As String
Dim i As Long
For i = 97 To 122
    S = Replace(S, Chr(i), "")
Next i
If S <> "" Then UppersAndDashes = Replace(S, Chr(32), "")
End Function

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF just like you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

 

[aarbogust]

Thanks to all. This worked perfect. I used one of the UDF's by JoeMO. Thanks again to each ad everyone. This site is amazing.

 

[Rick Rothstein]

Function UppersAndDashes(S As String) As String
Dim i As Long
For i = 97 To 122
    S = Replace(S, Chr(i), "")
Next i
If S <> "" Then UppersAndDashes = Replace(S, Chr(32), "")
End Function

This worked perfect. I used one of the UDF's by JoeMO.

More than likely your data is such that this won't matter, but just in case... JoeMo's UDF above will retain digits and symbols other than dashes along with upper case letters and dashes. If that could matter to you, then here is an alternate UDF that only retains upper case letters and dashes as you originally asked for...

Function UppersAndDashes(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!A-Z-]" Then Mid(S, X) = " "
  Next
  UppersAndDashes = Replace(S, " ", "")
End Function

 

[JoeMo]

More than likely your data is such that this won't matter, but just in case... JoeMo's UDF above will retain digits and symbols other than dashes along with upper case letters and dashes. If that could matter to you, then here is an alternate UDF that only retains upper case letters and dashes as you originally asked for...

Function UppersAndDashes(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!A-Z-]" Then Mid(S, X) = " "
  Next
  UppersAndDashes = Replace(S, " ", "")
End Function

Given that the OP stated: "Basically extract Uppers and Dashes OR remove Lowercase letters and spaces", "just in case" coding didn't appear necessary.

 

[Rick Rothstein]

Given that the OP stated: "Basically extract Uppers and Dashes OR remove Lowercase letters and spaces", "just in case" coding didn't appear necessary.

It has been my experience that OPs do not always take into account all the various nuances when they ask a question. For example, what if the OP's list has some names with middle initials (with the period after them)? Or what if the rapper will.i.am is in his list?

 

[aarbogust]

You guys are awesome. Since the program that exports these reports to excel is very uniform in the way that I needed, Im still sticking with the original UDF. However, I have a new problem that I will post to a new thread.
Basically im getting external data from a table online and i need to know how to combine some of the information that is being extracted on separate rows underneath the primary rows of information. Information always changes so I need something(macro or UDF) that is flexible as the primary rows change.
I will explain more in new thread. Hope to see all of you there!!