Special characters in Excel

[philk12]

I have an XL sheet with 6 fields..Im converting xl into a table.
Among 6fields, 2 fields have problem i.e. first name(field3) and last name(field4) have got special characters & digits & quotes & slashes & #...
I got to remove them & should have only alphabets from A to Z in first & last names...
Please suggest me various techniques how do I this?

 

[SkipVought]

Hi,

Check out Edit/Replace.

Skip,

_[red][/red]

 

[philk12]

Skip, yes this is true, but how would U automate this?
Thanks,
Philky

 

[SkipVought]

Turn on your macro recorder and have at it.

Skip,

_[red][/red]

 

[hisbadbanana]

right click on your worksheet tab and choose "View Code"

type this code

Private Sub Worksheet_Change(ByVal Target As Range)
Cells.Replace What:="&", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="#", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

make a new line for each character to want to eliminate.

make sure you use a new line for each row that begins with "cells."

 

[philk12]

I copied the source you sent but dont know what to do next.
Iam just failing to save as macro and run it further.
Just new to XL sheet modifying.....
Can you tell me what to do once after I paste that script?

 

[Gavona]

Take Skip's advice. Then compare the code you get with hisbadbanana's. You'll learn more that way.

Tools, Macro.......

You could also look at thread68-1291783

and at the Excel functions Trim, Clean and Substitute.
In excel 2003 searching help for "substitute" and choose "Remove spaces and nonprinting characters from text" from the resulting list of topics.


Gavin

 

[philk12]

Thanks very much for all the pointers.
I am attaching my xl sheet with just the only column I require.
FIRST_NAME has got digits,quotes,dots,special characters etc, I need to remove them and have only characters from A to Z(first_name obviously does not contain letters other than A to Z ). Please do let me know how you removed them & I am trying your steps suggested and hope to come out with flying colors...... I am able to record the macro but am unclear as to how to set it up as a recurring function, that is, something that woulid run as a command of some kind.


FIRST_NAME

(JAMES) SKIP
*
*
*
*
*
*
*
*
*
*
*
-PEDRO
.
.
..
..
..
..
..
..
00ALLEN
00BEN
00BRETT
00CATHERINE
00DIANE
00J
00JOHN
00JOHN
00JUDITH
00LORI
00MARCO
00MARYA
00NICHOLAS
00RAM
00RICHARD
00ROWIE
00STEVE
00TALMAGE
01BRADLEY
01GREG
01HAROLD
01LINDA
01MALENA
01MARK
01MICHELLE
01MORGAN
01RAM
01TOD
01WILLIAM
02AMANDA
02ANN
02BENJAMIN
02CHRISTINA
02DAVID
02EDMOND
02KAREN
02KATHLEEN
02LAUREN
02LISA
02LYNN
02MATTHEW
02ROBERT
02STACY
02TIMOTHY
02TRAVIS
02W
03ANDREW
03BRENT
03DARIN
03DIANA
03DOROTHY
03FRANK
03HENRY
03JOHN
03JOHN
03KENNETH
03THOMAS
03TOMOYUKI
04ANTHONY
04ASGAR
04BRANDON
04BRIAN
04CAROL
04CHERYL
04CHRISTOPHER
04DALLAS
04DOUGLAS
04GLADYS
04JAMES
04JOHN
04MONICA

 

[SkipVought]

Is this a recurring issue or is it a one-time event?

Skip,

_[red][/red]

 

[KenWright]

Variation on Dave McRitchie's Trimall Macro

Sub Replace_All_But_Letters()

    Dim x As Long
    Dim cell As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    On Error Resume Next   'in case no text cells in selection
    
    Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
                      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    
    For Each cell In Range("A3:A31,A33:A64,A91:A96,A123:A255")
        x = cell.Row

        Selection.Replace what:="~" & Chr(x), Replacement:="", lookat:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=True
    Next
    For Each cell In Intersect(Selection, _
                               Selection.SpecialCells(xlConstants, xlTextValues))
        cell.Value = Application.Trim(cell.Value)
    Next cell
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

As for what to do with it, try here:-

So where exactly do I put this macro code then??
faq707-5758

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[hisbadbanana]

i retried with my own advice and the change macro failed to run.

i had to do the following to make it work.


close excel.
reopen with just the workbook you're working on.

do as i said before but this time copy and paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
For x = 0 To 9
    Cells.Replace What:=x, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next x
Cells.Replace What:="&", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="#", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

go back to your worksheet with your data and type in one more new entry.


p.s.

maybe someone with more excel experience can figure out why the on worksheetchange macro needed excel to restart?

 

[KenWright]

You would of course have to select all the data that my code was to be applied to before you ran it.

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

please can we keep code questions in the VBA Forum (Forum707). If an answer requires code, the post should be made within the VBA forum

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[philk12]

)I right clicked on sheet1 and wrote this macro.
2)I came to XL sheet, clicked record new macro by giving name Macro1
3)i stopped macro recording
4)i ran the macro but
nothing has happened. Tell me is it something wrong on what Iam doing.
This is same I ve been doing from yesterday night but was nt successful at all..

 

[hisbadbanana]

the code works when i use it.

i suggest you take geoff's advice and repost in the vba forum, ask why you can't get that code to run.


thanks
hisbad