Consulting

Results 1 to 6 of 6

Thread: Solved: Adding and apostophe to the beginning of numbers within cell

  1. #1
    VBAX Mentor Hoopsah's Avatar
    Joined
    Nov 2007
    Posts
    303
    Location

    Solved: Adding and apostophe to the beginning of numbers within cell

    Hi

    We currently have an Excel spreadsheet containg various info.

    In column E we have a unique identifier in the form of 9 digits, these are still all preceded by zeros i.e. 000324555

    What happens is we have to copy the data into another program and unless all of these zeros have been preceded by an apostrephe i.e. '00324555 then it is copied over as 324555 (not correct)

    Does anyone know of a basic formula or macro that will allow me to put an apostrophe at the start of each number within that column (I suppoes it should ensure that there isn't already an apostophe there already)

    Thanks for your help
    I am playing all the right notes, but not necessarily in the right order.
    Eric Morecambe

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]
    For i = 1 To Cells(Rows.Count, "E").End(xlUp).Row

    Cells(i, "E").Value = "'" & Cells(i, "E").Text
    Next i
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Bob,

    Would it help to add code so the macro adds a leading apostrophe only if there isn't one there already? What about placing the code as part of a worksheet_changeeevent() so he doesn't have to remember to run the macro? (Am I gilding the lily?)

    Cheers,
    Ron
    Windermere, FL

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Ron,

    Certainly makes sense to check for the apostrophe first. I don't think event code is necessarily the best way though as he said it needs to copy it to another program, so a button driven macro seems good to me.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Bob,

    Point taken, makes sense.

    Thanks,
    Ron
    Windermere, FL

  6. #6
    VBAX Mentor Hoopsah's Avatar
    Joined
    Nov 2007
    Posts
    303
    Location
    Hi Bob,

    as usual - works perfectly.

    Thanks for the help and for the input from Ron.

    This was purely a one off to help a colleague so I am more than happy to hold it as a macro button.

    Thanks Again

    Gerry
    I am playing all the right notes, but not necessarily in the right order.
    Eric Morecambe

Posting Permissions

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