VBA/Formula to find cells prefixed with an apostrophe

[stephjayne165]

Hey Guys,

After hours of googling and not being able to find anything that works for me, I need your help!!

I have come to the conclusion that what I want to do is not possible by formula, and therefore I'm looking at VBA. However, my VBA knowledge is severly limited at best, so I'm going to explain what I want, and hope that on eof you understands and is able to provide me some much needed assistance!

I need to calculate the below for each row in a column of data

IF the field contains only numbers THEN, IF the field is prefixed with an apostrophe, RETURN true/false.

So essentially, if the field is numbers only, is the number prefixed with an apostrophe? yes or no.

The first section I can do with an IF statement and ISNUMBER formula. It's the second part I'm having difficulties with.

Any questions, please let me know.

And, as always, thank you for your assistance, it is very much appreciated!!

Cheers,
Steph

 

[brucef2112]

.........I have come to the conclusion that what I want to do is not possible by formula, and therefore I'm looking at <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>.
.........I need to calculate the below for each row in a column of data
IF the field contains only numbers THEN, IF the field is prefixed with an apostrophe, RETURN true/false.
So essentially, if the field is numbers only, is the number prefixed with an apostrophe? yes or no.

This can be done with a formula.
For clarity, You want to look for a REAL apostrophe (known as a Smart Apostrophe), and not the single quote mark (next to the Enter key) known as a Dumb Apostrophe.

[COLOR=#444B51][FONT=Museo Sans 300]Dumb apostrophe is a straight mark, the kind used to denote a foot measurement and the kind most often used when typing on a keyboard. 
Smart apostrophes have a clockwise curve at the bottom. [/FONT][/COLOR]

For everyone, the Smart Apostrophe is created by turning Num Lock ON, then while holding down the [Alt] key, type in the number 0146 using the number pad (not the keys above QWERTY).
Let go of the Alt key. You now have a Smart Apostrophe.
This information in not readily known and is important for this formula to work.

I will assume when the OP said 'field contains only a number', and wants to test for an apostrophe, that he is referring to the everything after the apostrophe (ie ’123).
as opposed to just a number without an apostrophe (ie 123)
Here is the formula:
=IF(COUNTIF(A1,"’*"),IF( NOT(ISERROR(MID(A1,2,LEN(A1))*1)),TRUE,""), "")
TAKE NOTE: The CountIf string contains a Real Apostrophe not a single quote mark.

It will return TRUE for a cell that contains an apostrophe, and has a valid number after it.

Real Apostrophe in column A AND is a number.     Real Apostrophe =  (with Num Lock ON, Alt+0146 on num pad)

  |  A     |   B  |
  |---------------|
1 |’abc    |      |
  |---------------|
2 |123     |      |
  |---------------|
3 |’234    | TRUE |
  |---------------| 
4 |’234A123|      |
|-----------------|

Formula in column B: 
=IF(COUNTIF(A1,"’*"),IF( NOT(ISERROR(MID(A1,2,LEN(A1))*1)),TRUE,""), "")

 

[peet338]

Hi Steph,

Luckily it is possible using a formula. Although it's not elegant.

A hack to convert a cell with a number in it prefixed with an apostrophe is to multiply it by 1 (i.e. '10000 * 1 = 10000). Therefore assuming there are only three posibilities for a cell value (alpha-numeric, number with an apostrophe prefix and number) you can use two nested if functions to test for your criteria.

first eliminate the alpha-numeric values by testing if multiplying the target value by 1 results in an error. Then eliminate numeric only values by testing if the target value is equal to the target value multiplied by 1 (the hack to remove the prefixed apostrophe).

Assuming your target value is in cell B2 the formula you can use is =IF(ISERROR(B2*1),"False",IF(B2=B2*1,"False","True"))

Hope this helps,

Pete

 

[brucef2112]

I just noticed the OP wanted:
TRUE for a cell with a number AND has an apostrophe
FALSE for a cell that had a number but no apostrophe.

My function just returned TRUE for cell with a number AND had an apostrophe. (all other returned empty string)

IF you really need the TRUE and FALSE answers, here is the modified formula.
=IF(COUNTIF(A1,"’*"),IF( NOT(ISERROR(MID(A1,2,LEN(A1))*1)),TRUE,""), FALSE)

Also want to qualify that I am using Excel 2003. I understand newer versions 2007+ have new functions which might be able to streamline the formula.

 

[Rick Rothstein]

If you are talking about the apostrophe located on the same key as the quote mark that is used to enter a number as a text value in a cell and if by number you mean a whole number (no decimal point), then you can use this UDF (user defined function)...

Function ApostopheNumber(Cell As Range) As Boolean
  ApostopheNumber = (Not Cell Like "*[!0-9]*") And (VarType(Cell) = vbString)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ApostopheNumber just like it was a built-in Excel function. For example,

=ApostopheNumber(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[brucef2112]

Steph requested a true/false returned if a number had an apostrophe. I took a literal approach of the question, so non-numbers should have nothing returned.
Peet's formula returns FALSE for numbers without an apostrophe AS WELL AS on Alpha, and Alpha-numeric cells.
Its just about the result's view. It may not matter to Steph

Q:Does cell have a *Number* with an Apostrophe?

  |Bruce Answers  |      |Peet  Answers  |
  |---------------|      |---------------|
1 |’abc    |      |    1 |’abc    |FALSE |
  |---------------|      |---------------|
2 |    123 |FALSE |    2 |    123 |FALSE |
  |---------------|      |---------------|
3 |’234    | TRUE |    3 |’234    | TRUE |
  |---------------|      |---------------| 
4 |’234A123|      |    4 |’234A123|FALSE |
|-----------------|      |---------------|

Bruce Formula: 
=IF(COUNTIF(A1,"’*"),IF( NOT(ISERROR(MID(A1,2,LEN(A1))*1)),TRUE,""), FALSE)

Peet Formula:
=IF(ISERROR(B2*1),"False",IF(B2=B2*1,"False","True"))

 

[stephjayne165]

Thank you all so much for your help!

Peet338 your formula worked perfectly.

I am now trying to copy the logic from your above responses but am running into some issues. These issues are probably exacerbated by my current jetlag and therefore lack of sleep - so bear with me!

I'm creating a template for upload into an internal system. This internal system is very picky with formatting and actually requires apostrophes prefixed in some of these fields. Therefore Im creating a test tab which mirrors the data in the template, but tests the formatting to ensure the system won't kick it out.

Therefore I'm trying to check for an apostrophe in two more rows (check all values - no dependant criteria) and want to return a true or a false value. The data is in the below format.
Row 1 - numerical, prefixed with 1-4 zero's
Row 2 - alphanumeric, always in format 1234A

Peets formula isn't working due to the preceeding 0s in row 1, and the alpha character in row 2. Brucef2112 I can't get your countif to work at all - I'm not sure if that's down to the formula ignoring the apostrophe due to the fact it's technically not there :/

Any light that can be she'd is much appreciated - please let me know if I can clarify anything

Thanks!

 

[brucef2112]

stephjayne165,
my formula was based on finding a REAL apostrophe (known as a Smart Apostrophe which isn't found on a keyboard and must be entered with an Alt+0146), and not the single quote mark (next to the Enter key) known as a Dumb Apostrophe.
So you are really looking for the apostrophe Excel uses as a prefix for text strings.

The apostrophe is not really a part of the cell's contents so Excel's functions like FIND or SEARCH Cannot be used to find it.
As such, changing my formula to look for a 'leading' apostrophe will not work. (Had it been a Smart Apostrophe, the type entered with Alt+0146, it worked great)

The only way to test for the leading apostrophe is with a UDF macro (Actually any VBA sub or function).

With the following code placed in a module, you can use this just like any other function in excel.
You would use it in your spreadsheet like this.
Your value to test for an apostrophe in A1.

In B1 the formula would be =FoundPrefixChar(A1)

You will get a result of TRUE/FALSE in B1.

place this in a module

Function FoundPrefixChar(aCell) As BooleanDim CurTransKeySetting As Boolean
    CurTransKeySetting = Application.TransitionNavigKeys
    Application.TransitionNavigKeys = False
    FoundPrefixChar = ("'" = aCell.PrefixCharacter)
    Application.TransitionNavigKeys = CurTransKeySetting
End Function

You can then use this as any other function in excel. It can also be used as a solution in your original question.

How to Add a module to your spreadsheet:
Open your spreadsheet you want to use this macro in.
Once opened, press Alt+F11 to open the Visual Basic Editor.
On the menu bar at the top , click on Insert > Module
you will see a Module1 added to the workbook (pane on left side)
on the right side you now have a white screen with the cursor blinking on it.
At the blinking cursor paste the above code.
you're done.
Go back to the spread sheet and use the function as described above.

 

[stephjayne165]

Thanks so much Brucef2112!

Apologies for my confusion around the smart apostrophe - makes perfect sense.

The vba code also makes perfect sense and I feel like I'm really close, however I'm getting a compile error when I insert the formula into the destination cells. Error states 'expexted:end of statement ' - I did everything exactly as instructed and copied your code into the module, editing nothing. When the error appears the text 'CurTransKeySetting' in the first line is highlighted.

What am I doing wrong?

VBA is really new to me, so apologies in advance if it's something really obvious!

 

[brucef2112]

Nahh, it wasn't you...
The forum sometimes messes with code placed inside the code tags.
The first line came out missing a carriage return just before the 'DIM'. So it should each part is on its own line.
example of previous posted code.
Function FoundPrefixChar(aCell) As BooleanDim CurTransKeySetting As Boolean
the red text belongs on second line. so it should look like this:

Function FoundPrefixChar(aCell) As Boolean
Dim CurTransKeySetting As Boolean
rest of the code blah blah
End Function

Here it is again. I've checked it after pasting and previewing, that it is the correct format for a copy/paste.

Function FoundPrefixChar(aCell) As Boolean
    Dim CurTransKeySetting As Boolean
    CurTransKeySetting = Application.TransitionNavigKeys
    Application.TransitionNavigKeys = False
    FoundPrefixChar = ("'" = aCell.PrefixCharacter)
    Application.TransitionNavigKeys = CurTransKeySetting
End Function