+ Reply to Thread
Results 1 to 10 of 10

Removing apostrophe

  1. #1
    Forum Contributor
    Join Date
    05-19-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    111

    Removing apostrophe

    Hi
    I have imported data from erp and the dates are carrying apostrophe ' .
    Tried the VBA code

    Sub foo()
    With Range("d:d")
    .Formula = .Value
    End With
    End Sub

    and other available thru net. But this is removing the apostrophe of date upto 13th. The rest its not able to remove.
    Can anyone help

  2. #2
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    291

    Re: Removing apostrophe

    Hi Faisal, sorry can we have more info please?

    Data from erp? Are you inserting a column next to it and removing the apostrophe'?

    I'm just a novice, but I actually think I might be able to help you here.
    If it's been helpful please mark as helpful

  3. #3
    Forum Contributor
    Join Date
    05-19-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    111

    Re: Removing apostrophe

    When we export the datas from the ERP, teh dates in the dates carries apostrophe' . Now since its been treated as a text, im not able to use any filtration in it.
    Now after exporting i need to get rid of this apostrophe' . So is it possible

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Removing apostrophe

    Quote Originally Posted by faisal.ta View Post
    Hi
    I have imported data from erp and the dates are carrying apostrophe ' .
    Tried the VBA code

    Sub foo()
    With Range("d:d")
    .Formula = .Value
    End With
    End Sub

    and other available thru net. But this is removing the apostrophe of date upto 13th. The rest its not able to remove.
    Can anyone help
    Try this...

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 01-02-2017 at 11:17 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  5. #5
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    291

    Re: Removing apostrophe

    Dunno what ERP is but you sure can remove the apostrophe with the substitute command. But as requested if you are doing this from a relative column you will need to code accordingly e.g.

    Column E contains D but with apostrophe removed:
    Range("E:E").Formula = "=SUBSTITUTE(RC[-1], ""'"", ""/"")"

  6. #6
    Forum Contributor
    Join Date
    05-19-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    111

    Re: Removing apostrophe

    Hi
    The Date in my D Column comes as '27-12-2016
    i need to remove this apostrophy

  7. #7
    Forum Contributor
    Join Date
    05-19-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    111

    Re: Removing apostrophe

    Quote Originally Posted by AlphaFrog View Post
    Try this...

    Please Login or Register  to view this content.
    Yes this worked
    Thank you

  8. #8
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    291

    Re: Removing apostrophe

    You'll have to insert Column D and convert that data, copy and paste the values and then delete column D.

    Range("E:E").Formula = "=SUBSTITUTE(RC[-1], ""'"", """")"
    Range("E:E").Copy
    Range("E:E").PasteSpecial xlPasteValues
    Range("D:D").Delete

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

    Re: Removing apostrophe

    Hello faisal.ta,

    Please also see this Thread: http://www.excelforum.com/showthread.php?t=1106411

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  10. #10
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,869

    Re: Removing apostrophe


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Removing leading apostrophe VBA
    By nigelbloomy in forum Excel General
    Replies: 0
    Last Post: 09-28-2015, 04:56 PM
  2. [SOLVED] How to removing apostrophe in blank cell?
    By vcl_over in forum Excel General
    Replies: 11
    Last Post: 05-21-2015, 08:21 AM
  3. [SOLVED] Remove preceeding apostrophe without removing preceeding 0s
    By nicklesr in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 10-15-2013, 02:01 PM
  4. [SOLVED] Removing Apostrophe
    By hammer2 in forum Excel General
    Replies: 2
    Last Post: 08-24-2013, 10:14 AM
  5. Excel 2007 : Cell format, removing apostrophe
    By jcmilton in forum Excel General
    Replies: 3
    Last Post: 03-08-2010, 07:38 PM
  6. apostrophe
    By Steve in forum Excel General
    Replies: 7
    Last Post: 01-12-2006, 09:45 AM
  7. Removing apostrophe from imported data
    By Rita Palazzi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-21-2005, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1