Inserting static characters at beginning and end of a cell content

[DoubleDiamond52]

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.

[#2 · Accepted 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.

[#3]

& of course. Didn't know about TRIM though so that is brilliant for future use. Thanks for your help.