remove ' from exported data

[hawilder]

Hello -

I have an excel spreadsheet with data that was exported from a database and all the fields have ' (apostrophe) in front of them. I need to remove this character from the last name and first name columns so I can consolidate them with 6 other spreadsheets (I've found that some of the names are duplicates on these sheets once they were all merged together the ' prevents the "remove dups" function from working -- only one of the spreadsheets has this so duplicate names show up when Ido remove dups.)

this is what it looks like.. I've tried everything i know possible

LName
'Smith
'Wilder
'Goon

Any help is much appreciated....

 

[JoeMo]

Select all the names. Press ctrl + h. In the dialog box that opens type ' in the Find What field and leave Replace With field empty. Click Replace All.

 

[hawilder]

thanks for your reply- I've already tried this "Excel cannot find any data to replace"

 

[Tom Urtis]

Take these steps and one of these methods should work.

From your worksheet, press Alt+F11 (careful, that's ALT + F11) which will take you to the Visual Basic Editor. Next, press Ctrl+G which will take your cursor into the Immediate Window.

Where the cursor is flashing, follow these steps:

Type in or paste this...
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
...and hit the Enter key.

Type in or paste this...
ActiveSheet.UsedRange.Replace what:=Chr(145), Replacement:=""
...and hit the Enter key.

Type in or paste this...
ActiveSheet.UsedRange.Replace what:=Chr(146), Replacement:=""
...and hit the Enter key.

Type in or paste this...
ActiveSheet.UsedRange.Replace what:=Chr(180), Replacement:=""
...and hit the Enter key.


Finally, press Alt+Q and see if those apostrophe-looking characters are gone.

The apostrophe character is ascii #39 and not a good idea to replace with nothing, as it will change text such as "It's Mom's and Dad's Anniversary" to "Its Moms and Dads Anniversary".

 

[John Davis]

Hello -

I have an excel spreadsheet with data that was exported from a database and all the fields have ' (apostrophe) in front of them. I need to remove this character from the last name and first name columns so I can consolidate them with 6 other spreadsheets (I've found that some of the names are duplicates on these sheets once they were all merged together the ' prevents the "remove dups" function from working -- only one of the spreadsheets has this so duplicate names show up when Ido remove dups.)

this is what it looks like.. I've tried everything i know possible

LName
'Smith
'Wilder
'Goon

Any help is much appreciated....

How about Data\Text-To-Columns\Delimited\Other '