Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Last week I started a thread requesting help on sorting a table containing merged cells.

    http://lounge.windowssecrets.com/ind...owtopic=769198

    Don (wdwells) very kindly produced a superb bit of code which did exactly what I wanted.

    However, I have a further requirement regarding adding missing leading zeros, and I have included the reference to my other thread just in case the merged cells has an effect on what I am trying to achieve.

    I need to sort a large table on Column A, but my (alpha-numeric) data has not been entered with leading zeros. All Column A reference numbers are (currently) 1 letter, then up to 3 numbers.

    I am looking for a piece of code which will change B2 to B002, and A12 to A012 etc, so the sort can then be carried out correctly.

    If anyone can help me with this, and even better, show me how to add this function into the start of Don's piece of code, I would be extremely grateful.

    I am attaching an example of a few rows from the large table I need to sort.

    Thanks

    Neil
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    If you add a column with the following
    =LEFT(A1,1)&TEXT(MID(A1,2,3),"000")
    it will re-format the data in Column A to give you a sortable set of values as you wish .

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Add this procedure:

    Code:
    Sub FixLeadingZeros()
      Const NumDigits = 3
      Dim strText As String
      Dim lngVal As Long
      LastRow = Cells(Rows.Count, 1).End(xlUp).Row
      For MainCtr = FirstRow To LastRow Step Merged
        strText = Cells(MainCtr, 1)
        lngVal = Val(Mid(strText, 2))
        Cells(MainCtr, 1) = Left(strText, 1) & Format(lngVal, String(NumDigits, "0"))
      Next MainCtr
    End Sub
    You can run it once, or you can call it at the beginning of SpecialSort:

    Code:
    Public Sub SpecialSort()
      Call FixLeadingZeros
      LastRow = Cells(Rows.Count, 1).End(xlUp).Row
      ...

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Many thanks, Hans and 'G'.

    Those should do the trick!

    Cheers

    Neil

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Is there a variant cell formula that will cope with leading zeroes? I had just formulated a somewhat complicated report so that the output had everything the state office wants, IN the required columns, WITH the leading zeroes.

    THEN the tech at the state office cheerfully informed me that their preference is to have spaces instead of leading zeroes. Most of the columns involve numbers which can be anything from 0 to 99999, so I need a formula that will mask with spaces the same way the current version masks with zeroes.


    Many thanks,

    Ann

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Can't you simply right-align the values?

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by axsc View Post
    I need a formula that will mask with spaces the same way the current version masks with zeroes.[/font][/size][/color]
    Assuming that the area of concern is not required as a value input for further calculations, the following code should do the trick. Select the range of interest then run:
    Code:
    Public Sub ReplaceLleading0s()
    Dim oCell As Range
    Dim ctr As Long
    
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
        For Each oCell In Selection
            For ctr = 1 To Len(oCell)
                If Mid(oCell, ctr, 1) = 0 Then
                    oCell = Left(oCell, ctr - 1) & " " _
                        & Right(oCell, Len(oCell) - ctr)
                Else
                    Exit For
                End If
            Next ctr
        Next oCell
    End Sub
    Note that this assumes the data are already packed with leading 0's
    Regards
    Don

  8. #8
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'll try the code and see what happens. What I have is ten source-data-from-the-field Excel sheets which I paste into a single workbook, and extract their data into two other worksheets we've been using for printed forms. To submit this data electronically, I need a line of eighty characters per well, and my leading-zeroes approach used =CONCATENATE to achieve that.

    I wonder if a non-breaking space might work? It might be easier to create in Excel, but I'd have to see whether the state office would be able to upload an 80-character row with non-breaking spaces.

    Because of the CONCATENATE, right-alignment doesn't work, alas.

    Ann

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by axsc View Post
    Because of the CONCATENATE, right-alignment doesn't work
    Why is that? I don't have any problem right aligning the CONCATENATE function results. Would something like
    =REPT(" ",80-LEN(VALUE(CONCATENATE(<mycells>))))&CONCATENATE(<m ycells>)
    work?
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by axsc View Post
    Is there a variant cell formula that will cope with leading zeroes? I had just formulated a somewhat complicated report so that the output had everything the state office wants, IN the required columns, WITH the leading zeroes.

    THEN the tech at the state office cheerfully informed me that their preference is to have spaces instead of leading zeroes. Most of the columns involve numbers which can be anything from 0 to 99999, so I need a formula that will mask with spaces the same way the current version masks with zeroes.


    Many thanks,

    Ann
    Hi Ann,

    Unless you're using a fixed-width font, changing the leading zeroes to spaces will cause alignment issues. That's because, although all digits have the same width in a given font, the space character is almost always a different width - typically (very) roughly half the digit width.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you, John, Hans, and Macropod!

    I may not have been as clear as I should have been in describing the problem: I could certainly right-align the 80-character concatenation and every one of its component parts, but while Excel is happy enough to mask the parts with leading zeroes and they appear in the concatenated cell just as they should, my attempts to replace leading zeroes with leading spaces have caused Excel to drop any leading spaces (and then there was the attempt that caused it to drop the text!).

    I'll try your formula, too.

    It isn't a font problem, as I formatted in Courier to be sure everything lined up correctly when working on the leading-zeroes version.

    Happy New Year,

    Ann

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hello Ann

    I have just realized that the CONCATENATE function strips out any leading spaces UNLESS the source data are formatted as Text. Having the data preceded with an apostrophe to force text behaviour is inadequate; Format > Cells > Number > Text is required.

    H.T.H.
    Regards
    Don

Posting Permissions

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