Replace All with conditions ... is it possible?

**Lanhoj** · 02-04-2010, 07:54 PM

Good evening,

I'm quite new with using Excel formulas and anything beyond the basics. However, I'm in need of some help quite urgently.

I was wondering if it was possible to "Replace All" but have conditions.

For example, if I want to replace all instances over multiple cells all that ' (apostrophe) and replace them with ` (French e accent) except for when the ' (apostrophe) is needed to be spelled correctly (EX: I'm, we're, they're, don't ...).

What would that formula be?

Example:
Cell 1 = You're 'going' home
Cell 2 = Okay, you'll see 'later'

I would want to keep the ' that is in "You're" and "you'll", but I want to replace the ' around "going" and "later" with ` instead.

Can someone please help?

Much appreciated,

Lanhoj

**teylyn** · 02-04-2010, 08:17 PM

Hi Lanhoi,

Welcome to the forum.

The main challenge here will be to figure out the rules that Excel should apply. The single quote can appear
at the beginning of a word: ('ere we go, 'fraid of nothing, 'fess up)
at the end of a word (several companies' products, the ladies' room)
or sandwiched between two letters (You're next)

The only safe rule would be to keep the sandwiched apostrophe, but as for the others: can you think of a rule to apply?

**Lanhoj** · 02-04-2010, 08:38 PM

The only rule I really need would be to keep the apostrophe when it's needed as part of a correctly spelled word. And when it's not, to replace the apostrophe with the French e accent.

" You're in the 'car's' garage " for example, should be:
" You're in the `car's` garage " with the apostrophe`s around " 'car's' " changing to the French e accent.

**JBeaucaire** · 02-04-2010, 08:45 PM

Humans know what words are spelled correctly and which are not when taking punctuation into account because we've spent years figuring it all out. Excel will only know exactly what you teach it within the macro. It will only obey instructions.

You have to fathom a logic that we can employ in the macro other than just "because we know it's right". Nobody's going to write a macro for you that individually deals with every possible use of an apostrophe for every possible word and position.

If you reread post #3, you've demonstrated only that a human knows it's right.

If you can define a set of exacting rules that generically explain the steps a macro should follow to determine whether an apostrophe should be replaced or not, we can help turn that reasoning into code for you.

**Lanhoj** · 02-04-2010, 08:56 PM

When an apostrophe is used in one of the following ways, it should remain unchanged:

're
've
'll
't
's
'm

If an apostrophe is used anywhere else, it should be replaced with an `

Would those rules be okay?

How would that be turned to code?

**teylyn** · 02-04-2010, 09:55 PM

Humans know what words are spelled correctly and which are not

Erhmm. Well, some do, some don't. Lately it looks like most don't

**BergShorn** · 02-04-2010, 10:27 PM

How about a UDF?

Code:

Function Grave(ByVal s As String) As String
    Static av As Variant
    Dim v As Variant
    Dim iPos As Long
    
    If IsEmpty(av) Then av = Array("'re", "'ve", "'ll", "'t", "'s", "'m")
        
    iPos = InStr(iPos + 1, s, "'")
    Do While iPos
        For Each v In av
            If InStr(iPos, s, v) = iPos Then
                Mid(s, iPos) = Chr(143)
                Exit For
            End If
        Next v
        iPos = InStr(iPos + 1, s, "'")
    Loop
    
    Grave = Replace(Replace(s, "'", "`"), Chr(143), "'")
End Function