Closed Thread
Results 1 to 11 of 11

Remove a leading Apostrophe

  1. 04-27-2010, 04:35 PM #1
    John Bates
    John Bates is offline
    Forum Contributor
    Join Date
    07-02-2008
    Location
    Utah
    Posts
    171

    Remove a leading Apostrophe

    Hi,

    I have around 300,000 cells (two colums by 150,000 rows) of text data that has an apostrophe leading each value. For example, I see the following:
    • John
    • Laura

    Though when I look inside each cell I actually see the following:
    • 'John
    • 'Laura

    I need to remove this leading apostrophe because it is messing up a formula that should reference these values and then perform a lookup in another table. How can remove these leading apostrophes? I've tried Find/Replace and RIGHT(A1,LEN(A1)-1) but these do not work. They do not recognize the apostrophe as part of the values in the cells.

    Thanks a ton!!
    John
    Reply With QuoteRegister To Reply
  2. 04-27-2010, 04:42 PM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Remove a leading Apostrophe

    That's a tough one. Excel just sees the ' as a guide to display the value as text (which they are anyway). On the flip side, the ' should not mess with your formulas. john = 'john VLOOKUP's should work fine unless something else is not aligned (i.e. blanks).
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 04-27-2010, 04:50 PM #3
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Remove a leading Apostrophe

    lookup should still work but try copying to another column paste special values then copy/paste back over original
    Last edited by martindwilson; 04-27-2010 at 04:52 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  4. 04-27-2010, 04:53 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Remove a leading Apostrophe

    Maybe even incorporate:

    SUBSTITUTE(A1,CHAR(39),"") into your formula where A1 is the reference cell with 'John
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  5. 04-27-2010, 05:10 PM #5
    day92
    day92 is offline
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: Remove a leading Apostrophe

    This macro will do the trick...

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 04-27-2010, 05:23 PM #6
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Remove a leading Apostrophe

    I like the macro. I will still put money on the fact it won't affect how your formula works.
    Reply With QuoteRegister To Reply
  7. 04-28-2010, 08:52 AM #7
    day92
    day92 is offline
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: Remove a leading Apostrophe

    How much money are we talking here?
    Reply With QuoteRegister To Reply
  8. 04-28-2010, 09:26 AM #8
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Remove a leading Apostrophe

    Here's a simple example of names from a table, some entered with ' and some without. The VLookup always pulls the first match from the table whether it has a ' or not and whether you put a ' or not into your input. LEN and SUBSTITUTE formulas also ignore the '.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 04-28-2010, 09:28 AM #9
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: Remove a leading Apostrophe

    @day92:

    LOL, That's the $20,000,000 question.

    @John Bates,
    why don't you post a file with some sample data to be inspected. There seem to be plenty takers...
    Microsoft MVP
    http://www.teylyn.com
    Twitter: http://twitter.com/IngeborgNZ
    Reply With QuoteRegister To Reply
  10. 09-09-2014, 07:36 AM #10
    1amgreen
    1amgreen is offline
    Registered User
    Join Date
    09-09-2014
    Location
    Bath, Somerset
    MS-Off Ver
    Microsoft Office 10
    Posts
    1

    Re: Remove a leading Apostrophe

    I had excactly that problem. The solution was just to copy and Paste Values for all the affected columns.


    Quote Originally Posted by John Bates View Post
    Hi,

    I have around 300,000 cells (two colums by 150,000 rows) of text data that has an apostrophe leading each value. For example, I see the following:
    • John
    • Laura

    Though when I look inside each cell I actually see the following:
    • 'John
    • 'Laura

    I need to remove this leading apostrophe because it is messing up a formula that should reference these values and then perform a lookup in another table. How can remove these leading apostrophes? I've tried Find/Replace and RIGHT(A1,LEN(A1)-1) but these do not work. They do not recognize the apostrophe as part of the values in the cells.

    Thanks a ton!!
    John
    Reply With QuoteRegister To Reply
  11. 09-09-2014, 07:41 AM #11
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Remove a leading Apostrophe

    4 year old thread and that solution was proposed in post #3
    Reply With QuoteRegister To Reply
Closed Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1