• Home
  • SEM
    • PPC
    • SEO
    • How To
  • Bangladesh
  • School
    • HTTP
    • REP
    • Bots n’ htaccess
    • Source Code
    • Excel 2010
    • HTML XHTML Entities
    • Gmail Operators
    • HTML & ASCII
  • About
    • Privy
    • About This Blog
  • Contact
  • বাংলা

Saidul Hassan

Digital Marketing Evangelist

Home / School / How to remove leading or trailing spaces in Excel 2010

How to remove leading or trailing spaces in Excel 2010

23 Aug, 2013 By Saidul Hassan · Filed Under: School

How to remove leading or trailing spaces in an entire column of excel worksheet in Excel2010 – Stack Overflow.

If you would like to use a formula, the TRIM function will do exactly what you’re looking for:

+---+-----------+--------------------+
|   | A         | B                  |
+---+-----------+--------------------+
| 1 | =TRIM(B1) | value to trim here |
+---+-----------+--------------------+

So to do the whole column…
1) Insert a column
2) Insert TRIM function pointed at cell you are trying to correct.
3) Copy formula down the page
4) Copy inserted column
5) Paste as “Values”

Should be good to go from there…

If there are other characters than “spaces” in what you’re trying to remove, you could try =TRIM(CLEAN(B1)) which will remove all non-printable characters and any leading/trailing spaces.

MS Excel: TRIM Function (WS, VBA)

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

Syntax

The syntax for the TRIM function is:

TRIM( text )

text is the text value to remove the leading and trailing spaces from.

Applies To

  • Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

  • Worksheet function (WS)
  • VBA function (VBA)

Worksheet Function Example

Let’s take a look at an example to see how you would use the TRIM function in an Excel worksheet:

Microsoft Excel

Based on the Excel spreadsheet above, the TRIM function would return the following:

=TRIM(A1) would return “Tech on the Net”
=TRIM(A2) would return “1234”
=TRIM(A3) would return “alphabet soup”
=TRIM(A4) would return “www.techonthenet.com”
=TRIM(”  apples “) would return “apples”

VBA Function Example

The TRIM function can also be used in VBA code in Microsoft Excel. For example:

Dim LResult As String

LResult = Trim ("   Alphabet   ")

The variable LResult would now contain the value of “Alphabet”.

Link to this post!
The following two tabs change content below.
  • Bio
  • Latest Posts
My Twitter profileMy Facebook profileMy Google+ profileMy LinkedIn profileMy Instagram profileMy Pinterest profile

Saidul Hassan

Managing Partner at Up Arrow Consulting
COO at Up Arrow Consulting, MCC manager, & Technical SEO consultant. Certified Google Partners and Microsoft Bing Ads Accredited Professional, Python enthusiast, wannabe SysAdmin. Graduated from School of Management & Business Administration (SOMBA), Khulna University.
My Twitter profileMy Facebook profileMy Google+ profileMy LinkedIn profileMy Instagram profileMy Pinterest profile

Latest posts by Saidul Hassan (see all)

  • Batch script to create directories & moving fixed number of files to those directories - 30 Apr, 2021
  • How to use PrismJS syntax highlighter on WordPress without plugin - 30 Mar, 2020
  • Download an Entire Website for Offline Viewing - 26 Nov, 2019

Share:

  • Twitter
  • Facebook
  • LinkedIn
  • Pinterest
  • Pocket
  • Email
  • Print

First published on 23 Aug, 2013 · Last updated 23 Aug, 2013 · Tagged With: Excel, MS Office

Batch script to create directories & moving fixed number of files to those directories

30 Apr, 2021 By Saidul Hassan

How to use PrismJS syntax highlighter on WordPress without plugin

30 Mar, 2020 By Saidul Hassan

Download an Entire Website for Offline Viewing

26 Nov, 2019 By Saidul Hassan

How to color highlight .htaccess files in Xed/Gedit

20 Aug, 2018 By Saidul Hassan

HMA Pro VPN Setup for Multiple Locations without User/Password Every time in Linux CLI

14 May, 2018 By Saidul Hassan

  • ♥ Bangladesh ♥
    Log in · Privacy Policy · Contact
    Copyright © 2011 Saidul Hassan

  • DMCA

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.