Remove characters

[Suraj Noorsai]

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

 

[macropod]

Hi Suraj Noorsai,

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

--
Cheers
macropod
[MVP - Microsoft Word]


in message news:[email protected]...

 

[pallaver]

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 =
"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

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

 

[Mike H]

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

 

[Ron Rosenfeld]

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

 

[macropod]

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

--
Cheers
macropod
[MVP - Microsoft Word]

Hi Suraj Noorsai,

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

--
Cheers
macropod
[MVP - Microsoft Word]

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