| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace apostrophes in text
Hello,
I have a worksheet that has some long text in a column of cells. Some of the words contain right-slant apostrophes ( ) which cause problems with the program that imports this data. Since there are many instances of this character that I need to replace with a standard apostrophe ( ' ), I thought I would do a find & replace to clean up the data. However, Excel will only find this character if I tell it to find in the "values". The replace only allows me to search on "formulas" and therefore doesn't find the characters to be replaced... Also, I have discovered that if I do get it to find some text it then complains about the formula being too long... Anyone know a solution to these problems? Thanks! Ian |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find & Replace apostrophes in text
Have you tried dragging the mouse over the right-slant apostrophe, copying
it, then used find & replace and pasting the RH apostrophe in the find box? John www.magicspreadsheets.com "R. Ian Lee" wrote: Hello, I have a worksheet that has some long text in a column of cells. Some of the words contain right-slant apostrophes ( ) which cause problems with the program that imports this data. Since there are many instances of this character that I need to replace with a standard apostrophe ( ' ), I thought I would do a find & replace to clean up the data. However, Excel will only find this character if I tell it to find in the "values". The replace only allows me to search on "formulas" and therefore doesn't find the characters to be replaced... Also, I have discovered that if I do get it to find some text it then complains about the formula being too long... Anyone know a solution to these problems? Thanks! Ian |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find & Replace apostrophes in text
Yes. That's what I've been doing and Excel finds no matches. It will work
if I "Look In" values during a Find. But when doing a Replace, I only get the option to "Look In" formulas. It can't seem to find the character in formulas. Thanks, Ian "John Lyons" wrote: Have you tried dragging the mouse over the right-slant apostrophe, copying it, then used find & replace and pasting the RH apostrophe in the find box? John www.magicspreadsheets.com "R. Ian Lee" wrote: Hello, I have a worksheet that has some long text in a column of cells. Some of the words contain right-slant apostrophes ( ) which cause problems with the program that imports this data. Since there are many instances of this character that I need to replace with a standard apostrophe ( ' ), I thought I would do a find & replace to clean up the data. However, Excel will only find this character if I tell it to find in the "values". The replace only allows me to search on "formulas" and therefore doesn't find the characters to be replaced... Also, I have discovered that if I do get it to find some text it then complains about the formula being too long... Anyone know a solution to these problems? Thanks! Ian |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find & Replace apostrophes in text
I have not tested it but you could try using the tilde character in front of
the apsotrophe. The tilde (~) allows you to look for special characters like the astrix... -- HTH... Jim Thomlinson "R. Ian Lee" wrote: Yes. That's what I've been doing and Excel finds no matches. It will work if I "Look In" values during a Find. But when doing a Replace, I only get the option to "Look In" formulas. It can't seem to find the character in formulas. Thanks, Ian "John Lyons" wrote: Have you tried dragging the mouse over the right-slant apostrophe, copying it, then used find & replace and pasting the RH apostrophe in the find box? John www.magicspreadsheets.com "R. Ian Lee" wrote: Hello, I have a worksheet that has some long text in a column of cells. Some of the words contain right-slant apostrophes ( ) which cause problems with the program that imports this data. Since there are many instances of this character that I need to replace with a standard apostrophe ( ' ), I thought I would do a find & replace to clean up the data. However, Excel will only find this character if I tell it to find in the "values". The replace only allows me to search on "formulas" and therefore doesn't find the characters to be replaced... Also, I have discovered that if I do get it to find some text it then complains about the formula being too long... Anyone know a solution to these problems? Thanks! Ian |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find & Replace apostrophes in text
you might check an excellent add-in from cpearson(dot)com[substitute . for
dot cell view then you can type in even not printing characters and find/replace works- very helpful if importing data from websites that may have funky formating "Jim Thomlinson" wrote: I have not tested it but you could try using the tilde character in front of the apsotrophe. The tilde (~) allows you to look for special characters like the astrix... -- HTH... Jim Thomlinson "R. Ian Lee" wrote: Yes. That's what I've been doing and Excel finds no matches. It will work if I "Look In" values during a Find. But when doing a Replace, I only get the option to "Look In" formulas. It can't seem to find the character in formulas. Thanks, Ian "John Lyons" wrote: Have you tried dragging the mouse over the right-slant apostrophe, copying it, then used find & replace and pasting the RH apostrophe in the find box? John www.magicspreadsheets.com "R. Ian Lee" wrote: Hello, I have a worksheet that has some long text in a column of cells. Some of the words contain right-slant apostrophes ( ) which cause problems with the program that imports this data. Since there are many instances of this character that I need to replace with a standard apostrophe ( ' ), I thought I would do a find & replace to clean up the data. However, Excel will only find this character if I tell it to find in the "values". The replace only allows me to search on "formulas" and therefore doesn't find the characters to be replaced... Also, I have discovered that if I do get it to find some text it then complains about the formula being too long... Anyone know a solution to these problems? Thanks! Ian |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find & Replace apostrophes in text
I'd already tried that too. Doesn't work.
Thanks, Ian "Jim Thomlinson" wrote: I have not tested it but you could try using the tilde character in front of the apsotrophe. The tilde (~) allows you to look for special characters like the astrix... -- HTH... Jim Thomlinson "R. Ian Lee" wrote: Yes. That's what I've been doing and Excel finds no matches. It will work if I "Look In" values during a Find. But when doing a Replace, I only get the option to "Look In" formulas. It can't seem to find the character in formulas. Thanks, Ian "John Lyons" wrote: Have you tried dragging the mouse over the right-slant apostrophe, copying it, then used find & replace and pasting the RH apostrophe in the find box? John www.magicspreadsheets.com "R. Ian Lee" wrote: Hello, I have a worksheet that has some long text in a column of cells. Some of the words contain right-slant apostrophes ( ) which cause problems with the program that imports this data. Since there are many instances of this character that I need to replace with a standard apostrophe ( ' ), I thought I would do a find & replace to clean up the data. However, Excel will only find this character if I tell it to find in the "values". The replace only allows me to search on "formulas" and therefore doesn't find the characters to be replaced... Also, I have discovered that if I do get it to find some text it then complains about the formula being too long... Anyone know a solution to these problems? Thanks! Ian |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find & Replace apostrophes in text
I really need to do this w/o an add-in since I'm going to need to pass this
information on to an analyst who will be making the actual changes. I'd prefer to not have to tell them to go install something... But, I'll certainly keep this in mind if there's no other solution. Thanks! Ian "reno" wrote: you might check an excellent add-in from cpearson(dot)com[substitute . for dot cell view then you can type in even not printing characters and find/replace works- very helpful if importing data from websites that may have funky formating "Jim Thomlinson" wrote: I have not tested it but you could try using the tilde character in front of the apsotrophe. The tilde (~) allows you to look for special characters like the astrix... -- HTH... Jim Thomlinson "R. Ian Lee" wrote: Yes. That's what I've been doing and Excel finds no matches. It will work if I "Look In" values during a Find. But when doing a Replace, I only get the option to "Look In" formulas. It can't seem to find the character in formulas. Thanks, Ian "John Lyons" wrote: Have you tried dragging the mouse over the right-slant apostrophe, copying it, then used find & replace and pasting the RH apostrophe in the find box? John www.magicspreadsheets.com "R. Ian Lee" wrote: Hello, I have a worksheet that has some long text in a column of cells. Some of the words contain right-slant apostrophes ( ) which cause problems with the program that imports this data. Since there are many instances of this character that I need to replace with a standard apostrophe ( ' ), I thought I would do a find & replace to clean up the data. However, Excel will only find this character if I tell it to find in the "values". The replace only allows me to search on "formulas" and therefore doesn't find the characters to be replaced... Also, I have discovered that if I do get it to find some text it then complains about the formula being too long... Anyone know a solution to these problems? Thanks! Ian |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
| Find & Replace in Text Box | Excel Discussion (Misc queries) | |||
| find latest date in a row, when dates have apostrophes in it | Excel Discussion (Misc queries) | |||
| Find & Replace text format | Excel Discussion (Misc queries) | |||
| Find/Replace Text In Cells | Excel Discussion (Misc queries) | |||
Linear Mode
