Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Removing errant apostrophes
I'm working with a spreadsheet produced by a scraper. For reasons that
I can only guess and am powerless to change, it places an apostrophe before some of the numbers (IOW, one might imagine that it believes them to be numeric strings). I was hoping to find a way to remove them en masse, through search and replace perhaps. But the search half of the process doesn't know how to look for an apostrophe at the beginning of a cell. It seems that those don't count. Any ideas on how this could be automated? |
#2
|
|||
|
|||
C Glenn Wrote: I'm working with a spreadsheet produced by a scraper. For reasons that I can only guess and am powerless to change, it places an apostrophe before some of the numbers (IOW, one might imagine that it believes them to be numeric strings). I was hoping to find a way to remove them en masse, through search and replace perhaps. But the search half of the process doesn't know how to look for an apostrophe at the beginning of a cell. It seems that those don't count. Any ideas on how this could be automated? Hi C Glenn In an adjacent column try =TRIM(cellreference) eg =TRIM(A1), this will remove the ' Then copy and paste special values -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=476035 |
#3
|
|||
|
|||
That works!
I created a cell with your suggested formula pointing to one of the errant cells, then copied it to a range of cells that would encompass all of the cells that were not converted properly. (Fortunately, they were contiguous; there were no cells having contents that would be mangled by the TRIM process.) Now, the rest of my workbook can simply point to those trimmed up cells. The data import process will have to include a macro that automates this step. Thanks. Chris. Paul Sheppard wrote: C Glenn Wrote: I'm working with a spreadsheet produced by a scraper. For reasons that I can only guess and am powerless to change, it places an apostrophe before some of the numbers (IOW, one might imagine that it believes them to be numeric strings). I was hoping to find a way to remove them en masse, through search and replace perhaps. But the search half of the process doesn't know how to look for an apostrophe at the beginning of a cell. It seems that those don't count. Any ideas on how this could be automated? Hi C Glenn In an adjacent column try =TRIM(cellreference) eg =TRIM(A1), this will remove the ' Then copy and paste special values |
#4
|
|||
|
|||
You have found a very pesky fact. Apostrophes in the middle of text are easy
to remove, but leading apostrophes are more difficult. Enter this tiny macro: Sub tickout() Dim r As Range For Each r In Selection r.Value = r.Value Next End Sub Select the cells you want leading ticks (apostrophes) removed and call the macro. It will remove ticks but is not effective against fleas -- Gary''s Student "C Glenn" wrote: I'm working with a spreadsheet produced by a scraper. For reasons that I can only guess and am powerless to change, it places an apostrophe before some of the numbers (IOW, one might imagine that it believes them to be numeric strings). I was hoping to find a way to remove them en masse, through search and replace perhaps. But the search half of the process doesn't know how to look for an apostrophe at the beginning of a cell. It seems that those don't count. Any ideas on how this could be automated? |
#5
|
|||
|
|||
This is amazing! It works on ticks but not fleas!?!?
Hey, thanks. This is great. Really bizarre though -- r.Value = r.Value removes the leading apostrophe. Also, I don't get that we would need to Dim r as Range. Seems like we should Dim c as Cell. Isn't Cell a valid concept in this context? I'm still a little new to Excel macros. Gary''s Student wrote: You have found a very pesky fact. Apostrophes in the middle of text are easy to remove, but leading apostrophes are more difficult. Enter this tiny macro: Sub tickout() Dim r As Range For Each r In Selection r.Value = r.Value Next End Sub Select the cells you want leading ticks (apostrophes) removed and call the macro. It will remove ticks but is not effective against fleas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert numbers with apostrophe's (ex. 219'2) to decimals? | Excel Worksheet Functions | |||
Removing trailing spaces from cells ? | Excel Worksheet Functions | |||
How do I stop excel removing zeros? | Excel Discussion (Misc queries) | |||
Removing ' character from cells | Excel Discussion (Misc queries) | |||
How do I obtain Curly quotes and apostrophes in Excel? | Excel Discussion (Misc queries) |