Results 1 to 8 of 8
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Removing an apostrophe from the first space in a cell in Excel

    In the attached zip is an Excel spreadsheet that I made a mistake. It is minor (I Hope).

    I converted all cells with a number by putting an apostrophe in the first space in the cell. This can be seen
    by the green triangle in the upper left hand corner of all the numerical cells in the Excel spreadsheet.

    There is also a green triangle in the cells that were originally text.

    I then accidentally saved the spreadsheet! I should not have! I now have an Excel spreadsheet
    with all numerical cells marked so as to be text. I do not want this. I should not have saved the
    spreadsheet after that action. I should have just clicked it away without saving it.

    Now I have created a button in the lower area of the spreadsheet that is the exact opposite of the button (and its VBA code)
    in the top of the spreadsheet.

    I want to remove all apostrophes from all numerical cells that have them. This would also remove apostrophes from the text cells.

    I created a button on the bottom and recopied the code that adds a apostrophe, but I tired to make one slight change in the line

    c ="'" & c.Value

    to

    c ="" & c.Value

    In other words remove the apostrophe from the selected cells, I can hopefully reverse the error that I made.

    The VBA code does not work!



    What changes must I make in order for the VBA code to work.

    Any help appreciated. Thanks in advance.


    Respectfully,


    Lou Reed
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Haven't looked at the sample, but how about:

    replace(c.Value ,"'","")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JonSmile is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    11
    you should be able to replace it with c= c.value
    and numbers will become numbers again (no green triangle)

    or
    Sub jon02()


    ActiveCell = ActiveCell.Value


    End Sub

  4. #4
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    What a minute. I am confused. I want to change, ie. remove, all apostrophe. So how should this line of VBA code read:

    c ="'" & c.Value .

    This is what is in there now. How should it be changed?

    Any help appreciated. Thanks in advance.


    Respectfully,


    Lou Reed

  5. #5
    JonSmile is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    11
    replace it with
    c = c.value

    See my example macro (using activecell instead of c)
    It is how excel treats values.

  6. #6
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Where is your example? I certainly want to see it.

    Respectfully

    Lou Reed

  7. #7
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, I found it in the other thread. Thank you very much.

    Respectfully,

    Lou Reed

  8. #8
    JonSmile is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    11
    welcome.

    Just to allow others who search and find this thread.

    This is the VBA to turn a text number into a real number
    Code:
    Sub jon02()
    
    ActiveCell = ActiveCell.Value
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Steal contents of Excel Cell
    By MatthewGrace in forum Programming
    Replies: 19
    Last Post: 09-24-2017, 01:02 PM
  2. Removing Carriage Returns when importing from Excel
    By Grant Shea in forum Programming
    Replies: 1
    Last Post: 08-28-2016, 12:58 PM
  3. Replies: 5
    Last Post: 08-03-2012, 11:38 AM
  4. Import single cell from excel
    By zippy483 in forum Import/Export Data
    Replies: 9
    Last Post: 02-24-2010, 02:16 PM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums