Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Remove apostrophe

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a away to write a macro that will remove the apostrophe in all selected cells. I currently have dates with apostrophes in front so that they will be seen as text. I need them as dates now and find replace doesn't seem to be working.

    Any ideas? Thanks

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-26 08:33, Adrae wrote:
    Is there a away to write a macro that will remove the apostrophe in all selected cells. I currently have dates with apostrophes in front so that they will be seen as text. I need them as dates now and find replace doesn't seem to be working.

    Any ideas? Thanks
    The option Data|Text to Columns should work when it is given ' as delimiter.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 08:33, Adrae wrote:
    Any ideas? Thanks
    One idea- could you use =datevalue(), format it to your liking and copy it down? This may require copy, paste special values and the deletion of your original column.

    I like this edit feature - it allows me to say to use the first (and best) idea from Aladin. They need an icon for a guy slapping himself on the forehead here!!!

    [ This Message was edited by: IML on 2002-03-26 08:47 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not just convert to date using Text To Columns (Data menu)?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this.

    Sub Remove()

    Range("B1:B10").Select
    Application.CutCopyMode = False
    Cells.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False
    Range("A1").Select
    End Sub

    Edit # 1: Sorry, didn't read it through, I thought you were removing 's from text

    [ This Message was edited by: Cosmos75 on 2002-03-26 09:56 ]

    Edit # 2:

    Sub Macro2()
    Range("B1:B10").Select
    Selection.TextToColumns
    Destination:=Range("B1:B10"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="'", FieldInfo:=Array(1, 3)
    End Sub

    [ This Message was edited by: Cosmos75 on 2002-03-26 09:59 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Mike T.
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What about Find-Replace

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have a look at articles Q124739 and Q124935 in the MS Knowledge Base.

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Andrea

    Just use:

    Sub DoIt()
    Selection = Selection.Value
    End Sub




  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 08:33, Adrae wrote:
    Is there a away to write a macro that will remove the apostrophe in all selected cells. I currently have dates with apostrophes in front so that they will be seen as text. I need them as dates now and find replace doesn't seem to be working.

    Any ideas? Thanks
    Hi Adrae:
    How about multiplying the number with the ' as the beginning character by 1 into the adjacent cell. For example if '3333 is in B17, then C17=B17*1 and then format the number in C17 as Date ...
    B17 C17
    '33333 33333 -->formatted as Date 4/5/91


    [ This Message was edited by: Yogi Anand on 2002-03-26 20:01 ]

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Just a follow up on Yogi's answer.
    Put 1 in random cell.
    Choose copy
    Mark your Date-cells
    Choose paste special
    Choose Multiply
    All of your dates are now multiplied by one and converted to values.
    Format them to dates

    regards Tommy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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