+ Reply to Thread
Results 1 to 4 of 4

Can I remove prefix apostrophe

  1. #1
    swansonwc
    Guest

    Can I remove prefix apostrophe

    Hi,

    I have a spreadsheet that is exported from a database, and all of the cells
    have an apostrophe prefix. The apostrophe keeps hyperlinks from functioning
    (since they are now considered text). Here is the code I have created to
    remove the apostrophe - unfortunately it removes the first actual character
    in the cell as well as the apostrophe:

    Public Sub ParseApostrophe1()

    Dim Cell As Range

    Worksheets("customers").Activate
    ActiveCell.CurrentRegion.Select

    For Each Cell In Selection
    If Len(Cell.Value) >= 1 And Cell.PrefixCharacter = "'" Then
    Cell.Value = Right(Cell, Len(Cell) - 1)
    End If
    Next Cell

    End Sub

    Does anyone have an idea how I can get rid of the apostrophe without
    deleting the first actual (visual) character in the cell.

    TIA

    Best regards,
    Bill


  2. #2
    Forum Contributor
    Join Date
    03-24-2005
    Location
    Wisconsin
    MS-Off Ver
    2007
    Posts
    378

    Maybe CTRL > H

    I have not tried to write a procedure to do what you are asking, but I have used the search & replace from within the spreadsheet to replace everything matching the search criteria.

    I don't know if any of your hyperlinks have an apostrophe in the address itself. Wouldn't seem likely?

    Make a backup copy before your do try this.
    Thanks!
    Dennis

    I am using Windows 7 and Office 2007, all of my posts are based on this.

  3. #3
    Dave Peterson
    Guest

    Re: Can I remove prefix apostrophe

    I'd replace:

    Cell.Value = Right(Cell, Len(Cell) - 1)

    with these lines:

    with Cell
    .numberformat = "@" 'keep it text
    .value = .value
    end with



    swansonwc wrote:
    >
    > Hi,
    >
    > I have a spreadsheet that is exported from a database, and all of the cells
    > have an apostrophe prefix. The apostrophe keeps hyperlinks from functioning
    > (since they are now considered text). Here is the code I have created to
    > remove the apostrophe - unfortunately it removes the first actual character
    > in the cell as well as the apostrophe:
    >
    > Public Sub ParseApostrophe1()
    >
    > Dim Cell As Range
    >
    > Worksheets("customers").Activate
    > ActiveCell.CurrentRegion.Select
    >
    > For Each Cell In Selection
    > If Len(Cell.Value) >= 1 And Cell.PrefixCharacter = "'" Then
    > Cell.Value = Right(Cell, Len(Cell) - 1)
    > End If
    > Next Cell
    >
    > End Sub
    >
    > Does anyone have an idea how I can get rid of the apostrophe without
    > deleting the first actual (visual) character in the cell.
    >
    > TIA
    >
    > Best regards,
    > Bill


    --

    Dave Peterson

  4. #4
    swansonwc
    Guest

    Re: Can I remove prefix apostrophe

    Dave,

    That worked perfectly.

    Thank you for your help.

    Best regards,
    Bill

    "Dave Peterson" wrote:

    > I'd replace:
    >
    > Cell.Value = Right(Cell, Len(Cell) - 1)
    >
    > with these lines:
    >
    > with Cell
    > .numberformat = "@" 'keep it text
    > .value = .value
    > end with
    >
    >
    >
    > swansonwc wrote:
    > >
    > > Hi,
    > >
    > > I have a spreadsheet that is exported from a database, and all of the cells
    > > have an apostrophe prefix. The apostrophe keeps hyperlinks from functioning
    > > (since they are now considered text). Here is the code I have created to
    > > remove the apostrophe - unfortunately it removes the first actual character
    > > in the cell as well as the apostrophe:
    > >
    > > Public Sub ParseApostrophe1()
    > >
    > > Dim Cell As Range
    > >
    > > Worksheets("customers").Activate
    > > ActiveCell.CurrentRegion.Select
    > >
    > > For Each Cell In Selection
    > > If Len(Cell.Value) >= 1 And Cell.PrefixCharacter = "'" Then
    > > Cell.Value = Right(Cell, Len(Cell) - 1)
    > > End If
    > > Next Cell
    > >
    > > End Sub
    > >
    > > Does anyone have an idea how I can get rid of the apostrophe without
    > > deleting the first actual (visual) character in the cell.
    > >
    > > TIA
    > >
    > > Best regards,
    > > Bill

    >
    > --
    >
    > Dave Peterson
    >


+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1