Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Punctuation?
Is there a simple way to strip a text cell of all its punctuation?
Thanks for any help! Ken |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Punctuation?
ASAP Utilities has a function that will strip out any characters you choose.
It's available free at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "kcoakley" wrote: Is there a simple way to strip a text cell of all its punctuation? Thanks for any help! Ken |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Punctuation?
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. "CLR" wrote: ASAP Utilities has a function that will strip out any characters you choose. It's available free at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "kcoakley" wrote: Is there a simple way to strip a text cell of all its punctuation? Thanks for any help! Ken |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Punctuation?
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 "Iskus23" wrote: 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. "CLR" wrote: ASAP Utilities has a function that will strip out any characters you choose. It's available free at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "kcoakley" wrote: Is there a simple way to strip a text cell of all its punctuation? Thanks for any help! Ken |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Punctuation?
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 "CLR" wrote: ASAP Utilities has a function that will strip out any characters you choose. It's available free at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "kcoakley" wrote: Is there a simple way to strip a text cell of all its punctuation? Thanks for any help! Ken |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Punctuation?
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 kcoakley wrote: 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 "CLR" wrote: ASAP Utilities has a function that will strip out any characters you choose. It's available free at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "kcoakley" wrote: Is there a simple way to strip a text cell of all its punctuation? Thanks for any help! Ken |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Punctuation?
kcoakley wrote...
Is there a simple way to strip a text cell of all its punctuation? 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,"[\-\[\]\^\\`~!@#$%&*()_=+{}|;:'"",<./?]+") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
how do i remove hyphens from between numbers | Excel Worksheet Functions | |||
Remove leading Numbers from text | Excel Worksheet Functions | |||
how to remove "(All)" in a pivot table using VBA | Excel Discussion (Misc queries) | |||
Remove Hyperlink | Setting up and Configuration of Excel |