If you import data from another program it often comes in with additional spaces either at the beginning, end or sometimes between words.
Thankfully the TRIM function will remove the excess spaces from text, except for single spaces between words.
How to use the TRIM function
It’s a simple function
=TRIM(text)
Where ‘text’ is the cell containing the text you want the additional spaces removed.
Examples of using the TRIM function
The image below shows three different scenarios in column B and the result of using the TRIM function in column C.
Note: in cell B5 I have highlighted the additional spaces at the end.
The formula in cell C4 is simply =TRIM(B4) and so on for rows 5 and 6.
Limitations of the TRIM Function
If you copy text from web pages and paste it into Excel the TRIM function will not remove non-breaking space characters commonly used in HTML web pages.
If you know HTML then you will recognise the non-breaking space as but when you copy it into Excel it simply appears as a space between your text.
If you need to remove this type of character or other non-printing characters you will need to use a combination of the CHAR function and the SUBSTITUTE function as follows:
Let’s say in cell A13 we have the following text with a non-breaking space at the beginning:
First some background as to how Excel interprets the non-breaking space.
Every character in Excel is given an ASCII value. The ASCII value for a non-breaking space is 160. But since the TRIM function cannot remove a non-breaking space we need to first convert it to a regular space that the TRIM function can deal with.
The ASCII code for a regular space is 32. So, that said our formula will be:
=TRIM(SUBSTITUTE(A13,CHAR(160),CHAR(32)))
The formula reads:
SUBSTITUTE the non-breaking space character, (CHAR(160)), with the regular space character (CHAR(32)), then TRIM the space from cell A13.
Got a Text problem in Excel? Post your question in the comments below and I’ll see if I can help.
{ 7 comments… read them below or add one }
Here’s a challenge for you, Mynda. I wonder if even a genius like you can answer it?
On my free ebook website, eBookTrove.com (absolutely free, in the spirit of the web) I need to remove the spaces from the links to the ebooks, but the space in ‘‘.
This is typical of the links –
<a href="AgathaChristie/14 - Miss Marple's Final Cases.pdf" rel="nofollow">Miss Marple's Final Cases.pdf</a><a href="AgathaChristie/11 - Three Act Tragedy.pdf" rel="nofollow">Three Act Tragedy.pdfWell, is it possible?
All the best
paul
P S The space between a href in the link has to stay, of course.
Hi Paul,
Thanks for your question. I use Excel all the time to update and edit HTML so I may be able to help you
1. The first thing I would do is use Text to Columns to extract the book names into a separate column using the greater than sign as the delimiter.
So you will end up with two columns:
Column A contains:
<a href="AgathaChristie/14 – Miss Marple's Final Cases.pdf" rel="nofollow"
<a href="AgathaChristie/11 – Three Act Tragedy.pdf" rel="nofollow"
Column B contains:
Miss Marple's Final Cases.pdf</a
Three Act Tragedy.pdf
CTRL+H to open Find & Replace dialog box > in the Find cell type a space > leave the Replace cell empty. Click ‘Replace All’.
I know this gets rid of all the spaces, even the ones you want to keep, but all you have to do then is put the spaces back in that you want. e.g. to fix a href, do a Find on ‘ahref’ and replace with ‘a href’, likewise for ‘.pdf”rel=nofollow”‘, replace with ‘.pdf” rel=nofollow”>’.
Don’t forget to do Column B to put the greater than sign back in for the the </a
So now column A & B are fixed.
3. Sort column A and B choosing 'Sort by' Column B.
Now your data is sorted by the book name.
4. In column C join the two columns back together using the ampersand. =A1&B1
5. Copy and paste column C as values.
Job done.
Let me know how you get on. If you get stuck send me your file.
Cheers,
Mynda.
please help,
i want to separate or delete the character enclose in the ()
Fundamentals of Accounting (ACCTG 1)
Partnership and Corporation (ACCTG 2)
Financial Accounting Problems 1 (ACCTG 21)
Financial Accounting Problems 2 (ACCTG 22)
Accounting (ACCTG(ECON) 1)
Basic Mechanical Engineering (ACE 01)
thank you very much
Hi Richard,
There’s a few ways to do this but one of the simplest ways to get rid of the () is to use ‘Find and Replace’. To open the Find and Replace dialog box press CTRL+H.
You might come unstuck on Accounting as it has two sets of ().
Alternatively you could use Text to Columns to separate the text or the SUBSTITUTE Function.
Hopefully one of those options is helpful to you.
Kind regards,
Mynda.
Please help me to extract all characters (alpha and numeric) up to the colon but without the “:” sign in the following. I have more than 13,000 rows to extract.
00276A: Maintain on-going professional and personal development
0044X: Imported unit 1 – lifestyle & leisure
AURV281208A: Carry out brazing procedures
Thank you.
Hi Alwi,
I’d use the Text to Columns tool. Use Delimited and colon as your delimiter. It’s much easier to use than trying to create a formula.
HTH.
Mynda.