Dec 07 2022 07:58 AM
Hi.
I am trying to clean up a spreadsheet before exporting it for import into another system. I've noticed some of the data includes spaces at the end of it and I need to identify all of those cells to correct them prior to export. For example, "surname " instead of "surname". The extra space will cause issues. Is there a way using formulas & functions to add a static character at the beginning and end of "surname" to highlight those cells with the extra space? So "surname " becomes "Xsurname X" so I can see the space. I'm thinking something like B$1$= 'X' + A$1$ + 'X' but it doesn't compute. Any help with the formula would be great thanks.
Dec 07 2022 08:51 AM
Solution@DoubleDiamond52 You could use ="X"&A1&"X" in B1, then fill down.
But I'd use =TRIM(A1) in B1 instead. This will remove leading and trailing spaces.
You can then use column B for the export.
Dec 07 2022 08:54 AM
Dec 07 2022 08:51 AM
Solution@DoubleDiamond52 You could use ="X"&A1&"X" in B1, then fill down.
But I'd use =TRIM(A1) in B1 instead. This will remove leading and trailing spaces.
You can then use column B for the export.