Identify cells with leading apostrophe via VBA

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,835
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
How do you detect if text in a cell starts with a leading apostrophe ? TIA.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Re: One last try... can anyone answer this

Maybe...

Code:
Sub xxx()
Dim c As Range
For Each c In Selection
    If c.PrefixCharacter = "'" Then
        MsgBox c.Address
    End If
Next c
End Sub
 
Upvote 0
Re: One last try... can anyone answer this

How do you detect if text in a cell starts with a leading apostrophe ? TIA.
I am not sure that you can; but, given that Excel hides it from you and from all output and calculations, why would you need to know this?
 
Upvote 0
Re: One last try... can anyone answer this

> why would you need to know this?
The worksheet stores text, which also exists in Access (where it is manipulated and corrections done etc).
A routine then compares Excel with Access, it they're the same do nothing, otherwise update Excel.
So you see the problem.. Excel says the data is different because of the missing apostrophe.
That could have been accepted and updated anyway but it bugged me I couldn't read exactly what the cell contained.
It seemed wrong, if a cell is formatted for text, that's what you should get, not part of it. So I was looking for some way to resolve that.

And it looks like Mark has it, c.PrefixCharacter does pick up the apostrophe, but as the cell isn't Selected, I'm trying some changes.

Thanks Mark & Rick.
 
Upvote 0
Re: One last try... can anyone answer this

but as the cell isn't Selected, I'm trying some changes.

I used Selection because you didn't state any particular range it was supposed to relate to :rolleyes:
 
Last edited:
Upvote 0
Re: One last try... can anyone answer this

I didn't have a range Mark, was using .cells(row, column). Now trying thus

Code:
                    Dim c As Range
                    For Each c In Worksheets("Sheet1").Range("C" & kRow)
                    Debug.Print c.PrefixCharacter
                    Next
As you can probably tell, I'm no expert. The For loop seems is only executed once, so maybe isn't necessary?
 
Last edited:
Upvote 0
Re: One last try... can anyone answer this

I didn't have a range Mark, was using .cells(row, column). Now trying thus

Code:
                    Dim c As Range
                    For Each c In Worksheets("Sheet1").Range("C" & kRow)
                    Debug.Print c.PrefixCharacter
                    Next
As you can probably tell, I'm no expert. The For loop seems is only executed once, so maybe isn't necessary?
Correct... the loop is not necessary for a single cell. If kRow has a value in it, then...

Debug.Print Worksheets("Sheet1").Range("C" & kRow).PrefixCharacter
 
Upvote 0
Re: One last try... can anyone answer this

Just to add a cell is a Range :biggrin: and you should specify it (even when using variables) when asking questions or else you liable to get things like Selection being used (despite how much we dislike selecting :ROFLMAO:).

Anyway hopefully you have it sorted now ;)
 
Last edited:
Upvote 0
Re: One last try... can anyone answer this

This is coming together even more nicely that ever hoped. My original line was
Code:
If (CStr(.Cells(kRow, i)) <> Nz(r(SM(.Cells(1, i))))) And ("'" & CStr(.Cells(kRow, i)) <> Nz(r(SM(.Cells(1, i))))) Then
can now be
Code:
CommaBit = IIf(i = 3, Evaluate(TitleCol).PrefixCharacter, "") 'TitleCol= "Sheet1!$B$178"
If CommaBit & CStr(.Cells(kRow, i)) <> Nz(r(SM(.Cells(1, i)))) Then

Only when i = 3 is existence of any 'leading comma' possible.
Speed isn't much different (doing 3000 rows) but it seems better code and Evaluate is working very nicely.
And knowing now about Prefix Character, I was able to Google and find a bit more.

Thanks to you both.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top