Remove Punctuation?

K

kcoakley

Is there a simple way to strip a text cell of all its punctuation?

Thanks for any help!
Ken
 
I

Iskus23

You can also do a find and replace. ("CTRL" + "H") Highlight the section
where you want to remove the punctionation and then find the characters and
replace them with nothing. You can remove all occurances at the same time or
you can review each change.
 
K

kcoakley

Hi Iskus23...

Thanks for the reply. While Find & Replace would work, it would require me
to do it once for each punctuation mark that might appear in my column of
text. I need to strip the punctuation from a column of 40,000 entries with
many different types of punctuation. I'll need to perform this task once a
month.

However, barring a simple formula or macro to do this, I could do a find and
replace and record it as a macro so I wouldn't have to redo it each month. It
would be tedious the first time, but then would work pretty smoothly.

Thanks for the idea!
Ken
 
K

kcoakley

Hi CLR...

Thanks for the reply. I've never heard of ASAP Utilities, but I downloaded
it and will give it a try. Thanks for the suggestion.

Ken
 
P

Pete_UK

You could also make use of the SUBSTITUTE function to get rid of up to
7 symbols in one nested formula. The formula can be copied down your
40,000 rows of data, but if you've got ASAP Utilities you might as well
use that.

Hope this helps.

Pete
 
H

Harlan Grove

kcoakley wrote...
Is there a simple way to strip a text cell of all its punctuation?
Click to expand...

An alternative add-in is Laurent Longre's MOREFUNC.XLL add-in, freely
available from

http://xcell05.free.fr/english/

It's REGEX.SUBSTITUTE function could be used in formulas like

=REGEX.SUBSTITUTE(A1,"[^0-9A-Za-z ]+")

to produce the text from cell A1 stripped of any characters that aren't
numerals, letters or spaces. Or you could specify the punctuation to be
removed,

=REGEX.SUBSTITUTE(A1,"[\-\[\]\^\\`~!@#$%&*()_=+{}|;:'"",<.>/?]+")
 
Top