Free Microsoft Excel 2013 Quick Reference

Remove Extra Spaces & Unwanted Characters

Imagine you have only one column - column A. In column A you have 500,000 + rows with text like the following:

!magnus
snooze'
sleepy/
act noun
act verb
Today&tomorrow
*bialgebras
/dev/null
devnull
1970;
Dwight Schrute

and your goal is to figure out a way to simultaneously transform the above rows into:

snooze
sleepy
act
act
Todaytomorrow
bialgebras
devnull
devnull
1970
DwightSchrute

I know I can use Filter and filter out characters like &'!/; but that takes too long because I have a lot of lists to go through with thousands of entries.

There must be a macro that will close spaces between words when only one space exists, but eliminate every word if 2 or more spaces exist between words - as well as eliminate unwanted characters like the ones I described above.

I know I could use substitute and eliminate the spaces between words
=substitute(a1," ","") but then I'd have to filter each and every instance of unwanted character. Any advice to filter these unwanted spaces and characters in my only column A?


Post your answer or comment

comments powered by Disqus
I am back again and, once again, have a long email list that I am going to
copy and paste from excel. For some reason this time every single cell has
about 30 extra spaces after the email address. So when I use the =j2&";"
function it puts the colon 30 characters after the email address... Any clue
how I can quick and easily get those spaces off of there??

Thanks in advance for any help...

I have a list with 1200 names. All the names unfortunately have a space on the end and I need to use them in a mail merge, but right now because of the space, the mail merge looks like this: Dear John Doe ,

I want it to look like this: Dear John Doe,

Isn't there a way to do this in one shot? Otherwise I'm going to have to do F2, backspace, enter, F2, backspace, enter...... 1200 times :'(

Edit: I found how to do this, but by solving another problem I was having. All the names were capitalized and I learned how to use the =PROPER() formula, which also removed the extra space for me. I love this site already :D

Can anyone help me?

I'm trying to remove the extra space between each of these words " 400 thread count egyptian cotton sheets" .
It doesn't look like it here but between every word i quotes there are two spaces instead of the normal 1. (the quotes are part of the text element and they stay)

Thank you

How do I remove the extra spaces so the numbers match when I run the pivot table. The total should be 4 instead of 2, since the numbers are the same.

Is there an easier way to do this beside going in front of the number and deleting the extra space.

I tried using the paint brush to match properties but it didn't work

Hello,

I was after a custom UDF function that deletes all spaces found in a cell containing text... all spaces means:
- single spaces between characters
- double, triple etc.. spaces between characters
- spaces found in the beginning and end of text

could someone help with this?

thanks
andy

I copied a bunch of economic data and I am trying to do calculations with it. Only problem is that the original source contained extra spaces, so Excel doesn't recognize the data as numbers.

For instance, instead of "9.9", it would be "9.9 " with the extra space. All the data in my spreadsheet is like this.

Is there any way to correct this all at once, without manually deleting spaces in the spreadsheet?

Hello All.
First ever post here so be gentle with me:-)

I have a problem
I have an excel sheet with approx 30,000 rows of data.
These are all keyword phrases.
They can be related to any subject, but for this example these are related to the root keyword phrase of "car Rent"

I have some software which basically pulls in keyword phrases from search engines and meta tags etc, including misspelled keyword phrases.

My problem;
The data is uncleaned.
In other words there might be I think it's called "Carriage return" data in there, so the row of data might be very deep (Instead of a row height of say 10.5 it could be anything, IE some could be 100 or 200 even).
There are unwanted characters, for example; ()[]{}+?!""^*

(If it could delete all unwanted characters except for letters/digits)
There is a problem I see, that if it removes _ or - between words, that it will join the words together which won't be of any use. If it deletes anything with a letter either side of it or a letter and digit, or 2 digits, 1 either side it would then need to add a space to replace the hyphen.

So for example; if there was a phrase in the list like
car_for rent
if it just removed the underscore, then the phrase would be
carfor rent
Which isn't correct. It would need to replace the underscore with a space.
I hope I'm making sense here:-)
So basically I'd love to have if possible a macro button that runs through my entire column of data,
(Always in Column A , on a sheet called "AllKWs", and always starting from row3 downwards.)

If it could go through the list and delete all unwanted characters including double spaces.
So the end result is a keyword phrase list without a lot of junk basically.
After it's gone through the list I suppose it needs to then look at what's left and delete any duplicate phrases last (As once some of these unwanted characters are removed, the keyword list may have duplicates).

Once all this is completed, can a pop up window appear saying something like;
=======================================

Starting No. Phrases: 29,745
Finishing No. Phrases: 29,722
No.Deleted Characters: 12,345
No.Deleted Carriage Returns: 234
No.Deleted Spaces: 235
No.Deleted Duplicates: 23

Time Elapsed: 7.78seconds
======================================
I think that's about it:-)
Not asking for much am I :-)
I really hope someone can help me out on this 1.
I can't write this for sure.
Out of my league I'm afraid
I hope it is possible as this would be very very useful for me.
Maybe it isn't possible as it is quite complicated.
If someone can have a look at it for me and have a go that would be brilliant

Thanks for your time.
Many Thanks
John Caines

Hello All.
I have a problem :-(
I have an excel sheet with approx 30,000 rows of data.
These are all keyword phrases.
They can be related to any subject, but for this example these are related to the root keyword phrase of "car Rent"

I have some software which basically pulls in keyword phrases from search engines and meta tags etc, including misspelled keyword phrases.

My problem;
The data is uncleaned.
In other words there might be I think it's called "Carriage return" data in there, so the row of data might be very deep (Instead of a row height of say 10.5 it could be anything, IE some could be 100 or 200 even).
There are unwanted characters, for example; ()[]{}+?!""^*

(If it could delete all unwanted characters except for letters/digits)
There is a problem I see, that if it removes _ or - between words, that it will join the words together which won't be of any use. If it deletes anything with a letter either side of it or a letter and digit, or 2 digits, 1 either side it would then need to add a space to replace the hyphen.

So for example; if there was a phrase in the list like
car_for rent
if it just removed the underscore, then the phrase would be
carfor rent
Which isn't correct. It would need to replace the underscore with a space.
I hope I'm making sense here:-)
So basically I'd love to have if possible a macro button that runs through my entire column of data,
(Always in Column A , on a sheet called "AllKWs", and always starting from row3 downwards.)

If it could go through the list and delete all unwanted characters including double spaces.
So the end result is a keyword phrase list without a lot of junk basically.
After it's gone through the list I suppose it needs to then look at what's left and delete any duplicate phrases last (As once some of these unwanted characters are removed, the keyword list may have duplicates).

Once all this is completed, can a pop up window appear saying something like;
=======================================

Starting No. Phrases: 29,745
Finishing No. Phrases: 29,722
No.Deleted Characters: 12,345
No.Deleted Carriage Returns: 234
No.Deleted Spaces: 235
No.Deleted Duplicates: 23

Time Elapsed: 7.78seconds
======================================
I think that's about it:-)
Not asking for much am I :-)
I really hope someone can help me out on this 1.
I can't write this for sure.
Out of my league I'm afraid:-(
I hope it is possible as this would be very very useful for me.
Maybe it isn't possible as it is quite complicated.
If someone can have a look at it for me and have a go that would be brilliant

Thanks for your time.
Many Thanks
John Caines

Hi,

I need help with a quick way to remove double spacing, extra spaces at beginning and end of text and also replace any special character with a space.

I have attached an example of bad data in column A and Column B shows how i would like it.

Is there a Formula or macros to help cleansed the data quickly?

Many thanks guys,

Mike

I have a column for comments
there are 900 rows (A1:A900)

this data is being imported from another source and some users have
extra spaces that I want to remove and place in (B1:B900)...I also want
to format it so that it is converted from all caps to all lowercase.

For example:

A1 reads "INT IN A FREE QUOTE ON 50000 CASH FOR DEBT
CONSOLIDATION"

B2 reads "int in a free quote on 50000 cash for debt consolidation"

--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=502511

I am looking for a formula that will remove any unwanted characters in a cell.

For example i may a word or string of words with "-" , " ' " or "," in them and i would like to have these removed.

Thanks,
Adam.

Is there a simple way to remove leading spaces from excel cells.

I have imported data using Crystal Reports from a database and a lot off the cells have up to twenty spaces (blank characters) before the data begins.

Any help of advice would be very much appreciated.

Kind regards,

Iain.

Hello All.
I am looking for a macro that I can run.
I am looking to delete characters only from the start of cells within Column A,
From A3 downwards, and the sheet is always called AllKWs
The macro would be placed on a different sheet to my data.
Ideally I would like to click an assigned button and a pop up
would appear. In this pop up I could type in for example the letter n
Followed by a space.
As I usually have say 30 to 40,000 rows of data, keyword phrases and there are always
some rows I need to clean up.
I have a great macro to clean unwanted characters and carriage returns etc,
but I really need this macro to delete unwanted characters at the START of cells.

So, in a spreadsheet I might have the following;
Code:
n best web design
n best web design and hosting
n best web design award
n best web design company
n best web design firm
n best web design program
n best web design software
n best web design template
n best web design tool
n best web design web site
hidden within a 40,000 row list of keyword phrases.
So, I am looking to delete all the n's at the start of the rows.
I cannot use the "Find & Replace" feature as if I type in n (Then Space)
it would also make design firm become desigfirm.

So, a macro in which a pop up would appear. In this pop up I can type in whatever I like,
number or letter, combination, a full word, and it would return a list of all
keyword phrases found within my AllKWs sheet which always starts from cell
A3 running downwards.

So in this example I click on the assigned macro button and I would type n (space)

It would then return a list of all the rows of keyword phrases starting with n (space)
from my AllKWs sheet

After these are returned it would ask," do I want to delete all the n (spaces) ONLY
from the returned list
I can click yes or no.
In this example I would click yes, and the above example would now look like this;
Code:
best web design
best web design and hosting
best web design award
best web design company
best web design firm
best web design program
best web design software
best web design template
best web design tool
best web design web site
That's it.
I'm not quite sure if this can be done the way I'm asking, but if it can it would be brilliant.

Many thanks
John Caines

Hi All,

I have a worksheet that has information in it that is imported from a .CSV file and the information shows like this:

******** ******************** ************************************************************************>Microsoft Excel - Resource Manager.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
CDEF175 Gainstron Road
Lytham

Home: 391184
Mobile: 07955551006
181 Barmon Avenue
Blackpool
FY2 9QLHome:
Mobile: 07996486669
Reports
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The problem I have is that in cell C17, when a post code has not been entered, there are extraneous CR and LF characters on the end. Is there a quick way to search through the entire column and remove them? Thanks for you help, in advance!

Is there a quick effective way to make sure a collum has two spaces after the text within it?

EG
A
1 xxx__
2 xxx__
3 xxx

Is there a way to remove all excess spaces in a column?

I have a report that i import, and it always has lots of extra spaces after the data in each cell.

anyone please?

Is there an excel function to cut x number of characters from the end of a line of text? I want to remove the last 5 characters from each cell (all in column A). These are not blank spaces. The characters (zip codes) are different on every line (there are about 500).

thank you!

Column A has characters such as - ' . @ # ? " ( ) % ^ & + How do I
remove all types of characters in this column? The only thing I want
is the text.

--
Scorpvin
------------------------------------------------------------------------
Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678
View this thread: http://www.excelforum.com/showthread...hreadid=490221

I export a report from a customer on the web. Their worksheet contains a
delivery schedule that I must compare to my schedule. I need to use a formula
to calculate the 20th day prior to their on dock date. The dates on their
worksheet have 2 additional unneeded spaces in the beginning of the date (in
each cell). I need to remove these spaces in order to format the cells and
then use a formula. I have tried everything from "Find & Replace" to retyping
each individual date. (There are about 2000 dates on this report).

Please help!

Hi

I'm trying to remove spaces from the end of text without success. A cell
contains for example: The(space)Cat(space)Sat(space)(space)(space)(space )
What i want is: The(space)Cat(space)Sat

What i am trying to do is search from the right, the first non-space
character, get it's position value in the overall cell length and with the
use of the Len function, remove all spaces after the final character. Please
note that the number of words in each text piece vary.

Any ideas please?
Rich

I have an Excel sheet that was created from a text file. It has a space in
each cell before the text. I have tried using the Trim function and it is
not removing the extra space. How can I remove the space without doing it
manually?

remove space from active cell
and display msgbox for numbers of spaces removed from that cell

Hi there,

Is there a way that you can remove a space character from the end of multiple cells? Please see the attached file. All cells containing numbers don't have a nummeric value because they contain a space at the end. I've manually removed the space from the end of D4 which allows me to calculate this cell.

I've had a look at Find/Replace but didn't find any way to remove the space from all cells. Short of removing the space manually from each cell, I don't know what else to do.

By the way, the data was copied from a web page which is why it's in this state.

Any help would be much appreciated.

Thanks,
Barbara

Hey all, and happy Easter!

Would some kind soul please offer me help on the following...?

I have 2 columns of data, for this excersize these columns are labelled column A and column B. I have just over 1300 rows of data for these two columns. Column A's data is as follows: 12 34.567 A (number number space number number dot number number number space character)

Column B's data looks like this: 001 23.456 B (number number number space number number dot number number number space character)

I need to convert the above to this: column A 1234.567 and column B 123.456

So, you'll see that I need to remove the space between the second and third numbers and the space and character after the 7th number in column A and then, the first two numbers, remove the space between the second and third numbers and remove the space and character after the 8th number in column B.

Please note that both columns of data are set out exactly the same way as described by the italics

Can I please have two formulas (one for each column) which I can then copy down.

Many, many, many thanks in advance

Ashley


No luck finding an answer? You could always try Google.