Forum Discussion
Solved
Inserting static characters at beginning and end of a cell content
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.
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.
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.
- & of course. Didn't know about TRIM though so that is brilliant for future use. Thanks for your help.
