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

Hi Suraj Noorsai,

For a string in A1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),"'","")

--
Cheers
macropod
[MVP - Microsoft Word]


"Suraj Noorsai" wrote in message ...
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

If you want that in Macro form - tweak the below as necessary. Also,
on a side note, how do you quickform notate A-Z and a-z instead of
writing it out longhand like I did.... I can't remember. Thanks.

Option Explicit
Const AlphaItems As String =
"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ"

Sub NameChanger()

Dim Position As Integer
Dim FullName As Variant
Dim ReturnName As String
Dim NameEntries As Range


Set NameEntries = Sheets("Sheet1").Range("A1:A3")

For Each FullName In NameEntries

MsgBox FullName
ReturnName = ""

For Position = 1 To Len(FullName) Step 1
If InStr(AlphaItems, Mid(FullName, Position, 1)) Then
ReturnName = ReturnName & UCase(Mid(FullName, Position, 1))
End If
Next Position

MsgBox ReturnName

Next


End Sub


On 7¤ë25¤é, ¤È«á4:36, 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

Hi,

on a side note, how do you quickform notate A-Z and a-z instead of
writing it out longhand like I did.... I can't remember. Thanks
For x = 1 To Len(temp)

Here's one way which may not be the best but it works.

For x = 1 To Len(temp)
If UCase(Mid(temp, x, 1)) = Chr(65) And UCase(Mid(temp, x, 1)) <= Chr(90)
Then
MyString = MyString + Mid(temp, x, 1)
End If
Next

Mike
"pallaver" wrote:

If you want that in Macro form - tweak the below as necessary. Also,
on a side note, how do you quickform notate A-Z and a-z instead of
writing it out longhand like I did.... I can't remember. Thanks.

Option Explicit
Const AlphaItems As String =
"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ"

Sub NameChanger()

Dim Position As Integer
Dim FullName As Variant
Dim ReturnName As String
Dim NameEntries As Range


Set NameEntries = Sheets("Sheet1").Range("A1:A3")

For Each FullName In NameEntries

MsgBox FullName
ReturnName = ""

For Position = 1 To Len(FullName) Step 1
If InStr(AlphaItems, Mid(FullName, Position, 1)) Then
ReturnName = ReturnName & UCase(Mid(FullName, Position, 1))
End If
Next Position

MsgBox ReturnName

Next


End Sub


On 7月25日, �後4:36, 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

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

Forgot about the upper-case conversion, for which you can use:
=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),"'",""))

--
Cheers
macropod
[MVP - Microsoft Word]


"macropod" wrote in message ...
Hi Suraj Noorsai,

For a string in A1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),"'","")

--
Cheers
macropod
[MVP - Microsoft Word]


"Suraj Noorsai" wrote in message ...
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