Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting (2000)

    I have a string of numbers that I need to format.

    Eg. 00000001025.00
    I need to have just 1025.00.
    The problem is that there is not a set number of 0's before the first number or a set amount of numbers before the decimal place.

    therefor, other possibilities are 001.34 which would have to be 1.34 and 00000102847.56 which would have to be 102847.56.

    Therefore, is there any combinations of functions that I could use that would handle all of these cases?

    If not, what VBA code would I have to write.
    Thanks everyone

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting (2000)

    Let column A house the numbers with leading zeroes.

    1. Select an unused cell.
    2. Edit|Copy it.
    3. Select all of the relevant cells in A.
    4. Activate Edit|Paste Special >Add.

    Aladin
    Microsoft MVP - Excel

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formatting (2000)

    Unless you change columns/rows before pasting, Activate Edit|Paste Special >Add would double the original number.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formatting (2000)

    Number strings such as you describe have probably been imported/input as text (ie with leading tick marks (')). The following macro, which is based on one from another post here at Woody's, converts all such 'values' on the active worksheet back to numbers, but without any overwriting formulae or genuine text you might already have. It also clears cells that, except for text tick marks, are empty:

    Sub ClearTickMarks()
    Dim CellContent, NewData
    For Each CellContent In ActiveSheet.UsedRange
    If CellContent.HasFormula = False Then 'leave cells with formulae alone
    If Len(CellContent) > 0 Then
    NewData = CellContent
    CellContent.FormulaR1C1 = NewData
    Else
    CellContent.FormulaR1C1 = "" 'clear tick marks from empty cells
    End If
    End If
    Next
    End Sub

    You should note that trailing 0s after decimla points will also disappear, but that's mainly a formatting issue - you can get them back by formating the cells to display whatever number of decimal points are required.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting (2000)

    Test (the instructions) before conclude...
    Microsoft MVP - Excel

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formatting (2000)

    Apologies - missed the bit about copying an empty cell <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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