[SOLVED] Need to find apostrophe not preceded by backslash

HowdeeDoodee

Well-known Member
Joined
Nov 15, 2004
Messages
592
I have a column of text where some apostrophes are not preceded by a backslash. I have a find and replace macro for these cells which are too long for excel's find and replace but the macro sometimes misses apostrophes in the cells. Can someone come up with a way to search down through the column to find apostrophes not preceded by the \ backslash charater. Thank you in advance for any replies.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

HowdeeDoodee

Well-known Member
Joined
Nov 15, 2004
Messages
592
I have solved this issue. Here is what I did.

I filtered the column by all those rows containing just an apostrophe.
I then filtered the column by all those rows not containing a backslash apostrophe.
What is left is just the rows containing only an apostrophe.
In an empty column next to the filtered column of text I entered the word "check" and filled down.
I then turned the filter off.
I then sorted the column with "check" in the column so all the rows with just an apostrophe were at the top of the sheet.
I then copied and pasted the cells containing only an apostrophe into wordpad.
Replaced every apostrophe with \', then replaced every \\' with \' so no double backslashes would appear.
 

HowdeeDoodee

Well-known Member
Joined
Nov 15, 2004
Messages
592
PLEASE NOTE!!!!!

This procedure works only if your length of text string in the cells does not exceed 255. If your text string is longer than 255, please see the following thread for samples of formulas you can use.

In one column use the formula to find the character without a backslash
In the next column use the formula to find the character with a backslash.
Compare colunms.
If value in one column does not equal value in other column then you have a character without a backslash.

http://www.mrexcel.com/board2/viewtopic.php?p=974102#974102
 

Forum statistics

Threads
1,089,570
Messages
5,409,055
Members
403,248
Latest member
KOH2031

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top