Share via

Trim Function Not Behaving as Expected

Anonymous
2022-08-05T15:48:09+00:00

I have a rather large Excel Spreadsheet that I want to Import (link) to an ACCESS Database. I did not create the spreadsheet. It probably was originally exported from some other type of database. The Excel Spreadsheet has a field in which the values are in the general form 0057A-0001-002. By that I mean that the values are a mixture of digits and Alphabet with some hyphens, but the lengths of the data strings may vary; there are no spaces except at the end. In any case, the data strings are text fields. There also are a varying number of spaces after the last character of the field. For this field to be useful to me in the database, I need to remove the trailing spaces. In previous work like this, I have used the TRIM( ) function in Excel to remove the extra spaces. Very simple to do, and it's always worked OK in the past. For some reason, today when I apply the TRIM function to something like "0057A-0001-002 " it does not give a result of "0057A-0001-002". It leaves the trailing spaces like "0057A-0001-002 ". My normal process would be to trim the field of interest in all the records, select all of the trimmed fields, copy them, then paste over them using the "Past Values" feature. I would expect then that when I check one of the newly pasted fields there would be no trailing spaces, but that is not happening. Any suggestions why this unexpected behavior. Here is a link to a small example of the spreadsheet -- the first 18 records or so. https://1drv.ms/x/s!AgnekDpFsyVfjJYs\_JeazBi7PKNXoA?e=IBRiL2

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

  1. Anonymous
    2022-08-05T16:12:43+00:00

    Re: "It leaves the trailing spaces"

    Most likely those spaces are Character 160 (called a non-breaking space).

    This formula removes almost all unnecessary characters...

    =SUBSTITUTE(CLEAN(TRIM(A2)), CHAR(160), "")

    This formula gets just the non-breaking spaces...

    =SUBSTITUTE(A2, CHAR(160), "")

    The 'Clean Data' utility in my free "Professional_Compare" workbook will do

    it for you without having to use a helper column.

    Download from OneDrive...

    https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

    '---

    Nothing Left to Lose

    100+ people found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Anonymous
    2022-08-05T16:32:09+00:00

    Your advice worked perfectly. Thanks. I though it probably had to do with hard spaces, but I did not know how to get rid of them.

    1 person found this answer helpful.
    0 comments No comments