0

I am working in a large excel doc. It has columns for first names and last names. I've concatenated the two columns, but noticed in the "First name" column some values have extra spaces at the end, which makes for awkward formatting:

John__Doe instead of John_Doe.

What formula can I use to identify all cells with a space as the final character, and then replace that space with nothing? What is the =if() formula?

Thanks

  • 1
    You can use Trim(A1) if A1 has space at the end it will be removed, if not it will remain the same – yass Jul 19 '17 at 19:21
  • as @yass said, you can use the Trim function, this function removes all the spaces except those ones between words, in other words if there are white spaces at the beginning or at the end it would be erased – Genaro Morales Jul 19 '17 at 19:42
  • Thanks so much, that solution worked. Once I'm done, how do I remove the function but keep the value in the cell? So it says "John" instead of "=trim()?" – S. B. Jul 19 '17 at 20:04
  • Copy the column with formula paste special values in another column – yass Jul 19 '17 at 20:17
1

Use =Trim() to remove extra spaces at the end of the cell.

  • Then copy the whole column with the Trim function
  • Paste Special Values in a new column
  • Delete the old column

You can also use the Trim in Concatenate together

=Trim(A1)&" "&Trim(B1)
0

You can nest the Trim function within your Concatenate function.

For instance;

=CONCATENATE(TRIM(A1)," ", B1)

This will complete your original task, but now it trims off any spaces in values of the first name.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.