2

Ive pasted a large list of data into column A in a excel sheet, all the data has come in with a space after the text ie. 'text ', 'some more text '..

I need to clear away this last space, as an approach i tried to find/replace all spaces, but that left me with a load of text strings like 'somemoretext' as it also deleted all the spaces in between words, any idea how i should handle this ?

sam
  • 9,486
  • 36
  • 109
  • 160

3 Answers3

5

TRIM In Microsoft Excel, the TRIM function returns a text value with the leading and trailing spaces removed.

Or you can still use Find and Replace option: Find "text " Replace "text".

zx8754
  • 52,746
  • 12
  • 114
  • 209
1

You can use logical approach: check last character 

(right(cell;1))

=IF(RIGHT(A3;1)=" ";LEFT(A3;LEN(A3)-1);A3)

If an space exists at the end, it will return the string -1 character (the space) if there isn't a space, it will return the text without change

Machavity
  • 30,841
  • 27
  • 92
  • 100
Maarten
  • 11
  • 1
  • 1
    i learned the semicolon is a regional setting, this code worked for me when I changed the semicolons to comma! – phenicie Oct 22 '15 at 17:47
0

You can also remove leading and trailing spaces from column name with this method.

Remove Leading and Trailing Whitespace from Excel Cells

See my answer there.

Naveed Khan
  • 338
  • 4
  • 16