On Fri, 25 Jul 2008 00:36:32 -0700, Suraj Noorsai wrote:

If I a have a anme like O'Brien or De La Hoya or
Van-Basten, how do I remove the charcters from the names. I want my answer to be OBRIEN, DELAHOYA AND VANBASTER

Here's a macro that works on the cells you have "Selected".

To enter this macro, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use the macro, select the cells which you want to process, then <alt-F8
opens the Macro Dialog Box. Select the CapsOnly macro and <RUN.

============================
Option Explicit
Option Compare Text
Sub CapsOnly()
Dim sTemp As String
Dim c As Range
Dim i As Long
For Each c In Selection
sTemp = c.Text
For i = 1 To Len(sTemp)
If Mid(sTemp, i, 1) Like "[!A-Z]" Then
Mid(sTemp, i) = " "
End If
Next i
c.Value = UCase(Replace(sTemp, " ", ""))
Next c
End Sub
===============================
--ron