VBA Macro question w/ specialization?

Question

Okay just a heads up I haven’t had any certified training for VBA and so as a matter of course, I do not have an exact understanding of the correct VBA terminology. Therefore, I will be doing the best I can to explain the desired result I wish to achieve without really knowing if my phraseology is correct or not. Sorry for the unintended difficulty; which hopefully is really just minimal.
The problem then I am trying to solve is selecting and then filtering internal specific data in a cell. For example:

35 66 78 42 Wachovia^^^
12 16 87 59 Wasatch^^
85 14 89 23 Williams****
64 03 22 48 Wilkinson`

These numbers, names, and symbols are all located inside the cell. I would like to find some macro formulas or commands that would assist me in isolating and then erase all things inside the cell (i.e. 12345^^^***```….) except for the names, giving me this end result::

Wachovia
Wasatch
Williams
Wilkinson

Answer 1

The following macro will achieve what you need. It uses column A for the data. If your column is not 'A', you will need to modify the code as follows:



Line 3: Change "A" to your column letter, i.e. "C"

Line 5: Change "A1:A" to your column letter, i.e. "C1:C"

Line 19: Change "A1" to your column letter, i.e. "C1"



Open the workbook



Copy this macro, modified if necessary, to the clipboard:



Sub StripCells()

Dim i, newVal, rng As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

Set rng = Range("A1:A" & LastRow)

For Each cell In rng

cell.Select

For i = 1 To Len(ActiveCell)

If Asc(Mid(ActiveCell, i, 1)) > 65 _

And Asc(Mid(ActiveCell, i, 1)) < 91 _

Or Asc(Mid(ActiveCell, i, 1)) > 96 _

And Asc(Mid(ActiveCell, i, 1)) < 123 Then

newVal = newVal & Mid(ActiveCell, i, 1)

End If

Next i

ActiveCell.Value = newVal

newVal = ""

Next

Range("A1").Select

End Sub



Press ALT + F11



INSERT > MODULE



Paste the macro into the module editing area to the right.



Close back to Excel.



Go to Tools > Macro > Macros



Highlight this macro, if not already highlighted.



Click 'Options..' (bottom right)



Select a letter to be used as a keyboard shortcut.



Close back to Excel.



Press CTRL + your letter to run the macro.

Answer 2

Depending on your version of Office, as the previous poster noted, having macros run when a workbook opens can be problematic. By default Excel does not allow macros to run without the user specifically allowing them. You can enable that behavior by modifying macro security and allowing all macros to run, but that it really not recommended. You could try placing a message on the first worksheet and then modifying that message once macro are enabled by the user, but that would require setting the message again on the workbook close event.

Answer 3

Can be done in excel without VB a little long winded but would work



by using the Mid function and splitting the text into individual cells and adding back only the ones wich have a asc code between 65 to 90 an d 97 to 122.





You would need to use the following functions...

=len(a1)

=CODE(MID($A2,C$1,1))

=c1&d1&e1 : adds cells back together (you would have to include a if statement to ensure the ones added back are only alphabetical, ie code between 65 to 90 an d 97 to 122.