apostrophe

I have zip codes which should have leading "0's" but excel automatically eliminates leading zeros. I realize that I could format the number to display 5 digits thus forcing a leading zero to appear. But that is just formatting and does not actually add a zero to the number. I want the...

Hi, I am having an issue with the code below. Every time it runs and I enter a company name with an apostrophe an error is thrown that reads 'type mismatch run time error 13'. I am fairly new to vba, I have however tried to double up on the quotation marks *If Evaluate("isref('"" & ActNm &...

I have an excel spreadsheet containing many rows copied from VBE. Some of these contain comments - always with a ' in column 1. I would like to delete these lines, but the ' is a hidden character and the program can not find it. Any suggestions, please?

Hello, I need to build the following formula, in column A I have legal name and in column B preferred name. If the Legal cell is blank, I want to return in C the value from B with two other conditions. Say in A1 the value is blank, in B we have D.O’Brien. What I need in C is D O'Brien I need to...

looking for best way to remove the feet('), inch(") and the hyphen from d3

I have a sheet that generates hyperlinks automatically based on other cell values using a custom column. It has been working fine until now, but I just started seeing some weird bugs in it. My custom column fomula is as follows: "'=HYPERLINK(""" & "URL<url>/files/J01009/" & [Account_Number] &...

I have a file of product numbers that contain both letters and numbers. Some of the products have an apostrophe in front of the first digit (Ex: '71245, or 'P1278) that is only visible in the formula bar when you select the cell. The apostrophe is not visible in the cell when unselected. I want...

Hi I've got dozens of cells where I need to remove the apostrophe and the '-' in the cell in order to make the data compatible with another system. Eg if the cell A1 had the text Goodfella's Greatest-Ever I'd want to use a formula in B1 that removes the apostrophe and the dash. Does...

Helping our resource center with a crazy project: an attorney has a table of data where 40% of the date values are like this "2003/00/00" and the rest are US-correct (03/15/2019). This one is hurting my brain, but I can't figure out how to (a) re-arrange the values and (b) get it to sort right...

This is the strangest thing I've seen yet Excel 2016 I have a particular range of cells that, no matter what I do, insist on replacing a formula with the numeric value of that formula UNLESS I enter the formula (manually) with an apostrophe in front of the "=" such as '=SUM(CC3/$BQ$24)...

How do you detect if text in a cell starts with a leading apostrophe ? TIA.

Within a range (column, containing both text and numbers) I have numbers displayed like: 0001 0010 etc. Cell properties -> custom show: 0000 I would like to add an apostrophe to those cells. However .. the result should still be that the leading zero's are displayed, i.e. the result would...

Hello May I ask forum if they could provide a formula to solve the below problem concerning leading apostrophe. I download data from a horseracing site and part of it contains the horses form figures and some but not all contain an apostrophe before the first number as in the below examples...

Hi Why when i type ' in a cell, then excel hides it, it only show in formula bar? i know if i do '100 then excel will take this as a text and if i want to show a formula then i type '=sum(1,1) is that what apostrophe used for? Thanks alot.

Hi all, I have a userform with a text field for users to enter a date. When they click "submit" on the form, the date is entered into cell E4. When I click on E4, it's showing that a hidden apostrophe has been added to the cell, preceding the date. The cell is set as Short Date format, so I'm...

Hello, I am getting the following error message "Syntax error (missing operator) in query expression". My search in google turned out that the cause of this error in my situation was an apostrophe being used. I have a bound data entry form with a field called Notes where the user can add as...

Hi everyone. If I type in 0712 into a cell, I get 712 If I format the cell first for 'Text', I get 0712 If I then edit the cell, and put an apostrophe before the 0712, the formula bar will show the apostrophe, but the cell contents will only show 0712. If I use a VBA code like this: For Each...

Hi, vba stops when trying to insert formula to a cell with string variable that have apostrophe in it In this formula ActiveCell.FormulaR1C1 = "=IF(RC7="""","""",IF('[" & TKNAME & "]aaa'!R[-3]C[18]="""","""",'[<wbr style="font-family: Arial, sans-serif; font-size: 12.8px;">" & TKNAME &...

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...

I am trying to assign a workbook name to a String variable Dim WeeklyRoutesWorkbook As String WeeklyRoutesWorkbook = ActiveWorkbook.Name When I try to call this later, I get an error Range("G2").FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[" & WeeklyRoutesWorkbook & "]Sheet1'!C5:C9,5,FALSE)"...