1. B

    Error is thrown when I enter a name in the input box with an apostrophe in it e.g. Son's

    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 &...
  2. R

    Remove commented lines

    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?
  3. V

    Assistance with Formula

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

    remove quotes and apostrophe from cell

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

    Weird bug in PowerQuery hyperlink generation?

    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] &...
  6. C

    Hidden Apostrophe in cell

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

    Removing specific characters from a cell?

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

    Sort Date column when month and day are unknown

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

    Cell Keeps Changing Formula to Value

    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)...
  10. A

    Identify cells with leading apostrophe via VBA

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

    Add apostrophe to a number with leading zero's - but keep leading zero's

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

    Removing Leading apostrophe in excel formula solution.

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

    apostrophe - what it means for excel

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

    Hidden Apostrophe Being Inserted when Userform Value Entered in Cell

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

    Apostrophe in Memo Field causing Synax Error

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

    Quirky formatting question

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

    Excel vba stops when the trying to insert formula to a cell

    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 &...
  18. S

    VBA/Formula to find cells prefixed with an apostrophe

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

    What to do when your String variable contains an apostrophe

    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)"...
  20. A

    Combine two or more columns by using a formula that contain apostrophe (')

    <tbody> A B C <tbody> MATARAM </tbody> <tbody> A1 </tbody> <tbody> 'A1' => 'MATARAM', </tbody> <tbody> BIMA </tbody> <tbody> A2 </tbody> <tbody> 'A2' => 'BIMA', </tbody> <tbody> DOMPU </tbody> <tbody> A3 </tbody> <tbody> ? </tbody> <tbody> PRAYA </tbody>...

Watch MrExcel Video

This Week's Hot Topics

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