Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: What makes Excel Add Trailing Blanks to Text

    I developed an Excel application run with VBA code that copies cells from one workbook to another. After the copy is made, the headings of the copied data are trimmed so that there are no leading or trailing blanks. These headings are recognized in an Access database. The spreadsheet is linked in the Access database, and the data gets appended to an Access table. The headings are referenced in an append query. Problem: the Access use of the Excel spreadsheet works flawlessly for me, but when my user in another state tries to run the application, the heading "CUST_NO" in the spreadsheet becomes "CUST_NO ", even after the VBA code does the trimming, and the Access append query fails because of the added spaces in the column heading.
    Question: what makes Excel add spaces to a heading, and how can I make Excel not do that for my user?

    I have even tried reassigning the values of the headings and Excel insists on padding the text (not for me but for my user), although only for the one column. The data in the column is a 10-digit number.

    Thank you.
    Jerry

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, Jerry. That shouldn't happen. What is in the trimming code? When you write: "when my user tries to run the application", do you mean, run an Access query to dump into Excel or an Excel query from the Access application?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    When the application starts from Excel, data in two of the tabs is changed some and copied into a separate workbook, the one that the Access database links to. This separate workbook is closed before opening Access.
    Even though steps are done to trim each heading, "CUST_NO" in the Excel heading is "CUST_NO " in the Access linked file. I use this code to trim:
    Code:
    Range("A1").Select
            
            For i = 1 To 11     'ensure that there are no extra spaces in the headings
                Cells(1, i).Value = Trim(Cells(1, i).Value)
            Next i
    This sounds wierd but could it be that Excel sees that all the data in the column are 10-digit numbers and is forcing the heading in cell A1 to be wider to take on the "width" characteristics of the data from the cells below? This does not happen on my PC but it does for the associate in another state who will use the application, and has the same company version (2003 SP2) of Excel.

    Jerry

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    From the Help:

    Sometimes text values contain leading, trailing, or multiple embedded space characters (Unicode character set (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.) values 32 and 160), or non-printing characters (Unicode character set values 0 to 31, 127, 129, 141, 143, 144, and 157). These characters can sometimes cause unexpected results when you sort, filter, or search. For example, users may make typographical errors by inadvertently adding extra space characters, or imported text data from external sources may contain nonprinting characters embedded in the text. Because these characters are not easily noticed, the unexpected results may be difficult to understand. To remove these unwanted characters, you can use a combination of the TRIM, CLEAN, and SUBSTITUTE functions.

    The TRIM function removes spaces from text except for single spaces between words. The CLEAN function removes all nonprintable characters from text. Both functions were designed to work with 7-bit ASCII, which is a subset of the ANSI character set (ANSI character set: An 8-bit character set used by Microsoft Windows that allows you to represent up to 256 characters (0 through 255) by using your keyboard. The ASCII character set is a subset of the ANSI set.). It's important to understand that the first 128 values (0 to 127) in 7-bit ASCII represent the same characters as the first 128 values in the Unicode character set.

    The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character.

    The CLEAN function was designed to remove the first 32 non-printing characters in the 7 bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters.

    To do this task, use the SUBSTITUTE function to replace the higher value Unicode characters with the 7-bit ASCII characters for which the TRIM and CLEAN functions were designed.
    Does this help at all?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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