How to clean Excel Cells from extra blanks

Simple problem: Importing external data to Excel Cell with values that include extra spaces either at the front or at the back of the values. I.e. Instead of having values like “My Value” the actual value is ”   My Value   “.

So how do we normalize the values?

Excel provides a function called Trim for this purpose exactly, the function receives a single value and returns the value with spaces trimmed on the left and right side of the value, while spaces in the middle of the string remain untouched.

Example Result
Trim ("   My Value") “My Value”
Trim ("My Value   ") “My Value”
Trim ("   My Value   ") “My Value”

See the Microsoft documentation about the Trim function here.

In addition there are function to remove spaces only at the front or only at the back of the value, see LTrim, RTrim, and Trim Functions.

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

1 thought on “How to clean Excel Cells from extra blanks”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s