Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add Character to Cell (2000 sp3)

    All,
    I have a need to add a leading zero to each cell in column A. I do not want to format the cell using the 'custom', as this does not truly add the zero as a real character. Any ideas? I have a list of several hundred numbers that require a real zero character.

    Thanks,
    Brad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add Character to Cell (2000 sp3)

    If you do so, the result will be text, not a number any more.

    Manually:
    Enter the formula ="0"&A1 in cell B1.
    Fill down as far as needed.
    Select the cells in column B.
    Copy to the clipboard.
    Select Edit | Paste Special...
    Click Values, then OK.
    Move the cells from column B to column A.

    Using code:

    Dim lngRow As Long
    For lngRow = 1 To Range("A65536").End(xlUp).Row
    If Not Range("A" & lngRow).Value = "" Then
    Range("A" & lngRow).Value = "0" & Range("A" & lngRow).Value
    End If
    Next lngRow

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Character to Cell (2000 sp3)

    Hans,
    As Always, Thanks!!!!!!! This did the trick. I will put this into my 'bag-o-macros'

    Brad

Posting Permissions

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