Free Microsoft Excel 2013 Quick Reference

Apostrophe

I am importing Excel data that has what seems to be blank cells but on closer
inspection I can see an apostrphe in the formula bar. That's not a problem
because I have written a macro to identify apostrophes and clear the contents
of these cells.
The mystery is that when the same data is imported on a friend's computer
with the same version of Excel the apostrophe does not appear in his formula
bar (although they are still non-blank cells) and so my macro will not work
on his computer since it doesn't see the apostrophes. Although I can write a
different macro in his case, I am interested to know what setting in Excel is
responsible for showing apostrophes in blank cells on some computers but not
others.

Thank you


Post your answer or comment

comments powered by Disqus
(I apologize if I'm not posting this twice, browser timed out)

To understand my question, let me explain what is happening.

I have a macro that reads through a list of 10 items on a worksheet.

It goes through the following loop where it reads each item in the list, and for each item in that list, the macro creates a new sheet and copies corresponding data to that sheet. It then renames the tab of that sheet based on data it just copied and finally creates a hyperlink from the list item to the newly created sheet.

The end result is a workbook with 11 sheets, the first sheet contains the list of items and the subsequent 10 sheets contain all the pertinent data for each of the items, one item per sheet.


	VB:
	
 
For x = 4 To 13 ' these numbers correspond to the row numbers in sheet 1
     
     ' get IP to filter on
     
    Sheets(1).Select 
     ' get the first IP in the list
    IP = Cells(x, 4) 
     
     
     '  check to see if a new sheet needs to be added and add new on if needed
     
    shcnt = Sheets.Count 
    For y = 1 To shcnt 
        cellcheck = Sheets(y).Cells(1, 1) 
        If cellcheck = "" Then 
            shcnt = y 
            Goto nxt2 
        End If 
    Next y 
     
    With ActiveWorkbook.Sheets 
        .Add After:=Worksheets(Worksheets.Count) 
    End With 
    shcnt = shcnt + 1 
     
nxt2: 
     
     ' select new sheet
     
    Sheets(shcnt).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I then copy a bunch of data to the sheet and I rename the sheet based on the data in cell A2


	VB:
	
 
shname = Trim(Range("A2")) ' remove any spaces surrounding data in cell A2
If Left(shname, 1) = "'" Then shname = Right(shname, Len(shname) - 1) 
If Right(shname, 1) = "'" Then shname = Left(shname, Len(shname) - 1) 
ActiveSheet.Name = shname 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Then I create a hyperlink from the corresponding item on sheet 1 to the newly created sheet containing all the data about that item copied from the DB.


	VB:
	
 
Hyperadd = Trim(Range("A2")) + "!A1" 
Sheets(1).Select 
Cells(x, 4).Select 
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Hyperadd, TextToDisplay:=IP 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So here is my problem:

The hyperlinks created by this process don't always work. The reason is, that the above process creates a hyperlink with just the name of the tab, but Excel sometimes adds a single apostrophe at the front and back which invalidates the hyperlink. You can see this when you create the hyperlink manually. Some tab names will have an apostrophe in the front and back and some won't. My attempt to detect and remove this apostrophe didn't work. Note the code to check for the apostrophe and remove it if found.

There does not seem to be a pattern to Excel putting the apostrophes. two different sheets with similar text in A2 will produce results where one gets the apostrophes and one doesn't.

Has anyone seen this and know of how to work around it?

I found this awesome code from an expired thread in Ozgrid and was able to tweak it some:


	VB:
	
 Remove_Char() 
    Dim rngData As Range 
    Dim varData As Variant 
    Dim i As Long 
     
    With ActiveSheet 
        Set rngData = .Range(.Range("A2"), .Range("A65536").End(xlUp)) 
    End With 
     
    varData = rngData.Value 
     
    For i = LBound(varData) To UBound(varData) 
        varData(i, 1) = Val(varData(i, 1)) 
    Next i 
     
    rngData = varData 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But, how can I get this code to apply to all unempty columns in a spreadsheet. For example, I need this code for a spreadsheet with 50 columns. Some columns have cells with the leading apostrophes and some don't.

Would you use a Do Until type statement and Loop?

Thanks in advance,

Giga_Me

I have a list of data that is 23K rows deep and need to match to eliminate duplicates. I know how to match but have been unable to format a key column to eliminate the leading apostrophe (e.g. 'KJM03390) from a mixed alpha-numeric field. Any help is gratefully received.

COUNTIF/SUMIF Wrong If Cell Has Single Apostrophes

In Microsoft Excel, when you use the COUNTIF and SUMIF functions, you may receive incorrect results if cells included in the range argument contain an apostrophe.

I needed to change a formula to text before copying it to other cells. I did this with a Replace command: Replace>Find what: =IF( then Replace>Replace With: '=IF( Now I need to remove the apostrophe to turn the text in all the cells back to formulas. When I use the Replace>Find what: '=IF( and Replace>Replace with =IF( I get an error message saying that "Microsoft Office Excal cannot find a match".

Dear forum members

On exporting information from another database to excel the fields which are text in nature, these are always exported as with an apostrophe at the beginning of text into the excel cell. I can do nothing about this as the export to excel is a defined routine which i have no control over.

Unfortunately due to the amounts of data, I am handling 10,000 lines, the physical removal of this apostrophe at the beginning of the text in each cell is not a good way forward.

The apostrophe is to indicate it is a text string, I have tried edit find and replace looking for the apostrophe with no luck.

It is important for me to remove the apostrophe as it then allows the text which is in fact a hyperlink to be live

eg in the cell 'http://www.blah.blah.com

I want the cell to display http://blah.blah.com and then the hyperlink will be live

any help is appreciated as i cannot change 10,000 cells manually

Arggghhhh!!!!!

thank you

thank you

yin

Hi,

I use the GetObjects function to gain access to the properties of a mailbox and want to fill out a table with some detail information. The code works fine until I have a mailbox which name has an apostrophe included.


	VB:
	
 o =
getobjects("winMgmts:!VS1rootMicrosoftExchangeV2:Exchange_Mailbox.LegacyDN=[B]'/O=TEST/OU=TEST/cn=Recipients/cn=co'brien'[/B],MailboxGUID='{59A40254-9F12-4502-9A03-3FB5CB0BBD38}',ServerName='VS1',StorageGroupName='sg1',StoreName='mb1'")


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I highlighted the section that causes the problem. The string within the string is encapsulated in apostrophes, hence the apostrophe in the mailbox name co'brien confuses the code and raises an error. Microsoft provides a solution by using escape characters, but that seems to work only in vbscript, not in vba. Here is a link to Microsoft's solution:

http://www.microsoft.com/technet/scr...4/hey1217.mspx

I tried some workarounds that are being used for a similiar problem with SQL statements as well, but again without success - these are the versions I tried:

co/'brien
co'brien
co''brien

Does anybody have some experience with this?

Thanks,
goppi

Need formula to strip apostrophe for lookup formula to recognize text as a number.

I have the following line of code in my VBA macro, where I'm trying to call a routine contained within another workbook.


	VB:
	
Application.Run Chr(39) & objWorkbook.Name & Chr(39) & "!FormatData" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem is that, in certain cases, objWorkbook.Name contains an apostrophe. I know that I can write a routine to strip out the apostrophe but this is going to cause other processing issues later in the process. I was hoping there's a simple way to modify this one line of code, since this is the only line of code causing problems for workbook names with apostrophes in them.

Thanks in advance for your help.
Rick

Hi,
Thanks for your help on previous threads.

I receive a lot of spreadsheets which contain numerical data where each cell has been forced to be text by a leading apostrophe.
Any ideas how to strip the apostrophe out so I can process the data, please?
I know the apostrophe is a hidden character, so I can't use Find & Replace, and using MID or RIGHT doesn't seem to work, either, even if I Copy and Paste Special as Values.
Is there an alternative to manually editing every single cell?

Many thanks for any advice.

Hello

I have a conditional macro that I use to check data entered on a form [invoice log table 4]. My checking looks at the supplier name [supplier name], and invoice number [invoiceno] fields, in a table [invoice log table], to see if the invoice already exists, and should thus not be entered again.
I'm doing this checking with the following bit of code as the condition


	VB:
	
 Null 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code works fine until the supplier name has an apostrophe in it, at which point an syntax error message pops up. I've tried to use 4 double quotes ("""") in front of and after the supplier name, which worked for the VBA part, but it doesnt seem to work here.

Looking forward to your bright ideas and thanks in advance.

Hi

I am attempting to paste a formula in a cell using VBA. The formula includes apostrophes "" as shown below:

Range("M15").Formula = "=IF(ISNA(VLOOKUP($M$3,'GC Price Book'!A:G,2,FALSE)),VLOOKUP($M$3&"",'GC Price Book'!A:G,2,FALSE),VLOOKUP($M$3,'GC Price Book'!A:G,2,FALSE))"

This is causing an error. If I remove the apostrophes in the body of the formula everything works fine.

It is necessary for me to paste this formula as the referenced sheet is periodically cleared, generating the #REF error in the formula. After the sheet is cleared the VBA macro repastes the formula.

Is there anyway to include the &"" component of the formula and paste it using VBA?

Tim

In my macro, I need to remove all apostrophes from the string except apostrophes in between letters. For example:

'there's some apostrophe's in 'this' string'

I want to remove the outside apostrophes and the apostrophes surrounding "this" but not the apostrophes in "there's" or "apostrophe's".

Any ideas?

Thanks.

Whenever I paste some data having leading apostrophe to Excel worksheet from a clipboard or after copying it from notepad, the leading apostrophe doesn't get hidden. Also when I double click any cell which has a leading apostrophe visible, they get hidden, but since I have huge amount of data copied I can't do this operation for all the cells. So is there any way to hide these apostrophes?
I can't use the replace functionality since I have leading zeroes in my data which would get deleted by that.

Hi,

I have data in one column which has numbers actually but showing text with apostrophe string. I want to convert this as numbers. Appreciate anybody help me.

Thanks

Khokhar

Hi:
I searched through some pages of old threads but could not find specifically one solution for performing the subject task on text strings in cells.

I have a lot of Excel files which contain both numerical data and text strings where each cell has been forced to have a leading apostrophe appended to the left side of the text strings and numbers.

How can I strip the leading apostrophe out?

I know the apostrophe is a hidden character, so I can't use Find & Replace.

I would like to figure out some VBA so that I could build this into and automate this via a macro I'm working on.

Is there a way to comprehensively do this for all cells containing text and numbers in a worksheet??? That would be great.

thanks much!
--Tom

I am dealing with a disk full of mp3 files and comparing the filename with the ID3 tag info to try and find where I have corruption. The problem I have is with the Queen Song '39 whose filename is of course
'39.mp3
Due to some archaic compatibility with older spreadsheets (I think) when this filename is entered into a cell and then referenced, the subsequent data extracted, drops the leading single quote mark and calls it 39.mp3
It does not matter if I format the cell to text before using, the same thing happens. The formula bar shows the apostrophe but the "Text" in the cell does not.
eg =A1 does not see this character so trying to navigate to the file by using its Pathname & Filename does not work. even Len() reports a character length of 6 and not 7
I can not find a switch to turn this off and would appreciate a way of getting the full name without resorting to re naming files etc.
Michael

I have added the Proper function to a cell which is generated by an if statement.

The cell before adding Proper looks like this BLAKE'S

After it looks like this Blake'S

How can I get around the problem obviously caused by the apostrophe ?

There will be apostrophe's throughout the workbook and I can't remove them.

I'm running a Macro that takes my spreadsheet and saves it as a text doc, the problem is that there are certain cells that i would like to save starting with apostrophes, e.g. 'factors, and excel doesn't allow me to type these in. is there any way that i can do this? Thanks.

Kate

Hello

I am working on a human resources type database for my team (60+ people).

Context:
Within the same workbook I have:
a list of team members in one worksheet (Firstname Surname) called "Team".a template on a worksheet called "Master" which creates a worsheet for each team member on "Team". All worksheets are named Firstname Surname - so John Smith would be an example of one of the worksheets createdI run a macro to copy the names on the "Team" worksheet to a "table of contents" worksheet - where each cell represents the name of one of the team form "Team".I then call another macro which converts these names on the Table of Contents to hyperlinks and these hyperlinks link to the individual team members worksheet (created by the above mentioned macro).So .... all of this works fine.

My problem is that 2 of the team members have an apostrophe in their surname - so John O'Leary for example.
The macros I have written create the worksheets for these 2 team members. Their names appear on the table of contents and "look" like hyperlinks as well.

When I select either of these two "hyperlinks" I get the "Reference is not valid" error.

Here is the relevant code I am using to create the hyperlinks on the "Table of contents" page (noting they are displayed in 6 columns depending on the level of the team member)


	VB:
	
maxRows = Range("B:G").SpecialCells(xlCellTypeLastCell).Row 
With Application 
    varTest = .Max(.Range("B8").End(xlDown).Row, _ 
    .Range("C8").End(xlDown).Row, _ 
    .Range("D8").End(xlDown).Row, _ 
    .Range("E8").End(xlDown).Row, _ 
    .Range("F8").End(xlDown).Row, _ 
    .Range("G8").End(xlDown).Row) 
End With 
 'maxRows = varTest
 'MsgBox maxRows
For c = 2 To 7 
    For r = 8 To maxRows 
        With ActiveSheet 
            If IsEmpty(.Cells(r, c)) Then Exit For 
            .Hyperlinks.Add Anchor:=.Cells(r, c), _ 
            Address:="", _ 
            SubAddress:="'" & .Cells(r, c).Text & "'!A1", _ 
            TextToDisplay:=.Cells(r, c).Value 
        End With 
    Next r 
Next c 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I would be most appreciative of ideas to fix.
Many thanks

Jackster

How could I run this script. I have words that have apostrophes, and this script errors out. I wish I could use (LIKE) but my table has different variations of this.
What I mean by this is that my table might have bucks, buck's, buck`s, therefore I canot use (buck%) to delete the row.

I Know that I can manually delete it but this but I am tired of deleting thousands of entries by hand and I am sure I will need it again in the future.

Delete FROM macno
where serno ='563'
AND
gnome ='BAKING BUCK'S';
commit;

I am trying to use the PROPER function to convert a list to initial caps, but it ignores words ending with Apostrophe S.

For example: MCDONALD'S converts to: McDonald'S

Is there any code to fix this?

Thanks

Larry

Re: Adding Comments (Comment lines begin with an apostrophe (') )

I know there is a way of doing this, however I can't find it....

How do i highlight a load of code (or text) and mark it as a "comment" all together????

I know this can be toggled on and off.

Anyone know?

Thanks & Regards

Hi

I need to format some statements in a workbook to comply with our German colleagues number format. Other statements need to maintain our local format.

UK format: 4.7 (#,##0.0,,_ ;[Red]-#,##0.0,,_ )
GERMAN format: 4'7

Very similar but our German office uses an apostrophe instead of a period symbol.

Any idea how I can change this format - I have experimented but I can't nail it.

Many thanks

Just want to know why I always get apostrophe (') before every text or in every sentence.

Ex. 'Supply, Install and Demolish.


No luck finding an answer? You could always try Google.