remove all the symbols

[elaine]

Hello

I have 2 problems here:

1. How do you remove all the symbols from a column?

Like, all of these: !"£$%^&*()_+-={}[]~;'#<>?,./¬`|\ without
deleting them one by one from a cell?

eg. Get "Titanic DVD Box Set" from "Titanic DVD [Box Set]"

Get only "5.99 pounds" from "£5.99 pounds"


2. How do you remove all the numbers from a cell?

eg. I only want to get the street names from a list of address:

Get only " oxford street" from "123 oxford street"
Get only "abbey road" from "2 abbey road"

Please help.

 

[Ardus Petus]

To clean up column B:

HTH
--
AP

'--------------
Sub CleanColumn()
Dim rCell As Range
Dim re As RegExp

Set re = New RegExp
re.Pattern = "[^\w \.]"
re.Global = True

For Each rCell In ActiveSheet.UsedRange.Columns("B")
rCell.Value = re.Replace(rCell.Text, "")
Next rCell
End Sub
'-----------------
"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hello

I have 2 problems here:

1. How do you remove all the symbols from a column?

Like, all of these: !"£$%^&*()_+-={}[]~;'#<>?,./¬`|\ without
deleting them one by one from a cell?

eg. Get "Titanic DVD Box Set" from "Titanic DVD [Box Set]"

Get only "5.99 pounds" from "£5.99 pounds"


2. How do you remove all the numbers from a cell?

eg. I only want to get the street names from a list of address:

Get only " oxford street" from "123 oxford street"
Get only "abbey road" from "2 abbey road"

Please help.

 

[Ardus Petus]

I forgot to mention my macro needs a reference to Microsoft VBScript Regular
E^xpressions 1.0

--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hello

I have 2 problems here:

1. How do you remove all the symbols from a column?

Like, all of these: !"£$%^&*()_+-={}[]~;'#<>?,./¬`|\ without
deleting them one by one from a cell?

eg. Get "Titanic DVD Box Set" from "Titanic DVD [Box Set]"

Get only "5.99 pounds" from "£5.99 pounds"


2. How do you remove all the numbers from a cell?

eg. I only want to get the street names from a list of address:

Get only " oxford street" from "123 oxford street"
Get only "abbey road" from "2 abbey road"

Please help.

 

[elaine]

Hi Ardus,

What is Microsoft VBScript Regular Expressions 1.0? Where should i get
that?
Is there an easier way to do that task?

thanks.

 

[Ardus Petus]

AFAIK, this is the esiest way to do it.

Press Alt-F11 to get VBE
Click Tools>References
Scroll down to Microsoft VBScript Regular Expressions 1.0
check box on the left
click OK

HTH
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,

What is ? Where should i get
that?
Is there an easier way to do that task?

thanks.

 

[elaine]

Hi Ardus,


I followed your intructions and run the code you provided, but it said:


Run-time error '94':
Invalid use of Null

Sorry about this....

AFAIK, this is the esiest way to do it.

Press Alt-F11 to get VBE
Click Tools>References
Scroll down to Microsoft VBScript Regular Expressions 1.0
check box on the left
click OK

HTH
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,

What is ? Where should i get
that?
Is there an easier way to do that task?

thanks.

I forgot to mention my macro needs a reference to Microsoft VBScript
Regular
E^xpressions 1.0

--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hello

I have 2 problems here:

1. How do you remove all the symbols from a column?

Like, all of these: !"£$%^&*()_+-={}[]~;'#<>?,./¬`|\ without
deleting them one by one from a cell?

eg. Get "Titanic DVD Box Set" from "Titanic DVD [Box Set]"

Get only "5.99 pounds" from "£5.99 pounds"


2. How do you remove all the numbers from a cell?

eg. I only want to get the street names from a list of address:

Get only " oxford street" from "123 oxford street"
Get only "abbey road" from "2 abbey road"

Please help.

 

[Ardus Petus]

I don't get this error!

When the error happens, click on Debug and tell me which line is yellow
underlined

TIA
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,


I followed your intructions and run the code you provided, but it said:


Run-time error '94':
Invalid use of Null

Sorry about this....

AFAIK, this is the esiest way to do it.

Press Alt-F11 to get VBE
Click Tools>References
Scroll down to Microsoft VBScript Regular Expressions 1.0
check box on the left
click OK

HTH
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,

What is ? Where should i get
that?
Is there an easier way to do that task?

thanks.

I forgot to mention my macro needs a reference to Microsoft VBScript
Regular
E^xpressions 1.0

--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hello

I have 2 problems here:

1. How do you remove all the symbols from a column?

Like, all of these: !"£$%^&*()_+-={}[]~;'#<>?,./¬`|\ without
deleting them one by one from a cell?

eg. Get "Titanic DVD Box Set" from "Titanic DVD [Box Set]"

Get only "5.99 pounds" from "£5.99 pounds"


2. How do you remove all the numbers from a cell?

eg. I only want to get the street names from a list of address:

Get only " oxford street" from "123 oxford street"
Get only "abbey road" from "2 abbey road"

Please help.

 

[elaine]

Theres no yellow highlighing in the code....

Let me tell you what I have done:

in Column B, I entered the following:

116 squadron
120 euros in pounds
166 sqn raf
2006 calendar
263 squadron
353 squadron r a f
617 squadron
63 sqn raf regt
737 simulator training
767-300 seat pitch
a340 aircraft for fs2004
a340 seating plan
aa
aa airlines
aa.co.uk
aa.com
aaib
aairlines
aberdeen - gatwick
aberdeen airport
aberdeen flights
aberdeen to amsterdam flights
aberdeen to belfast flights
aberdeen to manchester
.... etc...

total of 5465 rows in Column B.

Then enter the code you provided, and also clicked the box next to
Mircosoft VBScript Regular Expression 1.0 and Ok it.

Then I click F5, and then the error message pops out, with no Yellow
highlighting.

I don't get this error!

When the error happens, click on Debug and tell me which line is yellow
underlined

TIA
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,


I followed your intructions and run the code you provided, but it said:


Run-time error '94':
Invalid use of Null

Sorry about this....

AFAIK, this is the esiest way to do it.

Press Alt-F11 to get VBE
Click Tools>References
Scroll down to Microsoft VBScript Regular Expressions 1.0
check box on the left
click OK

HTH
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,

What is ? Where should i get
that?
Is there an easier way to do that task?

thanks.

I forgot to mention my macro needs a reference to Microsoft VBScript
Regular
E^xpressions 1.0

--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hello

I have 2 problems here:

1. How do you remove all the symbols from a column?

Like, all of these: !"£$%^&*()_+-={}[]~;'#<>?,./¬`|\ without
deleting them one by one from a cell?

eg. Get "Titanic DVD Box Set" from "Titanic DVD [Box Set]"

Get only "5.99 pounds" from "£5.99 pounds"


2. How do you remove all the numbers from a cell?

eg. I only want to get the street names from a list of address:

Get only " oxford street" from "123 oxford street"
Get only "abbey road" from "2 abbey road"

Please help.

 

[Ardus Petus]

Works by me...
I have Windows XP Pro and Office 2000

If y have Windows XP, I can log into your system and debug the problem.

HTH
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Theres no yellow highlighing in the code....

Let me tell you what I have done:

in Column B, I entered the following:

116 squadron
120 euros in pounds
166 sqn raf
2006 calendar
263 squadron
353 squadron r a f
617 squadron
63 sqn raf regt
737 simulator training
767-300 seat pitch
a340 aircraft for fs2004
a340 seating plan
aa
aa airlines
aa.co.uk
aa.com
aaib
aairlines
aberdeen - gatwick
aberdeen airport
aberdeen flights
aberdeen to amsterdam flights
aberdeen to belfast flights
aberdeen to manchester
.... etc...

total of 5465 rows in Column B.

Then enter the code you provided, and also clicked the box next to
Mircosoft VBScript Regular Expression 1.0 and Ok it.

Then I click F5, and then the error message pops out, with no Yellow
highlighting.

I don't get this error!

When the error happens, click on Debug and tell me which line is yellow
underlined

TIA
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,


I followed your intructions and run the code you provided, but it said:


Run-time error '94':
Invalid use of Null

Sorry about this....

AFAIK, this is the esiest way to do it.

Press Alt-F11 to get VBE
Click Tools>References
Scroll down to Microsoft VBScript Regular Expressions 1.0
check box on the left
click OK

HTH
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,

What is ? Where should i get
that?
Is there an easier way to do that task?

thanks.

I forgot to mention my macro needs a reference to Microsoft VBScript
Regular
E^xpressions 1.0

--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hello

I have 2 problems here:

1. How do you remove all the symbols from a column?

Like, all of these: !"£$%^&*()_+-={}[]~;'#<>?,./¬`|\ without
deleting them one by one from a cell?

eg. Get "Titanic DVD Box Set" from "Titanic DVD [Box Set]"

Get only "5.99 pounds" from "£5.99 pounds"


2. How do you remove all the numbers from a cell?

eg. I only want to get the street names from a list of address:

Get only " oxford street" from "123 oxford street"
Get only "abbey road" from "2 abbey road"

Please help.

 

[Ardus Petus]

Could you upload your workbook on http://cjoint.com and post the link?

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Theres no yellow highlighing in the code....

Let me tell you what I have done:

in Column B, I entered the following:

116 squadron
120 euros in pounds
166 sqn raf
2006 calendar
263 squadron
353 squadron r a f
617 squadron
63 sqn raf regt
737 simulator training
767-300 seat pitch
a340 aircraft for fs2004
a340 seating plan
aa
aa airlines
aa.co.uk
aa.com
aaib
aairlines
aberdeen - gatwick
aberdeen airport
aberdeen flights
aberdeen to amsterdam flights
aberdeen to belfast flights
aberdeen to manchester
.... etc...

total of 5465 rows in Column B.

Then enter the code you provided, and also clicked the box next to
Mircosoft VBScript Regular Expression 1.0 and Ok it.

Then I click F5, and then the error message pops out, with no Yellow
highlighting.

I don't get this error!

When the error happens, click on Debug and tell me which line is yellow
underlined

TIA
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,


I followed your intructions and run the code you provided, but it said:


Run-time error '94':
Invalid use of Null

Sorry about this....

AFAIK, this is the esiest way to do it.

Press Alt-F11 to get VBE
Click Tools>References
Scroll down to Microsoft VBScript Regular Expressions 1.0
check box on the left
click OK

HTH
--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi Ardus,

What is ? Where should i get
that?
Is there an easier way to do that task?

thanks.

I forgot to mention my macro needs a reference to Microsoft VBScript
Regular
E^xpressions 1.0

--
AP

"elaine" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hello

I have 2 problems here:

1. How do you remove all the symbols from a column?

Like, all of these: !"£$%^&*()_+-={}[]~;'#<>?,./¬`|\ without
deleting them one by one from a cell?

eg. Get "Titanic DVD Box Set" from "Titanic DVD [Box Set]"

Get only "5.99 pounds" from "£5.99 pounds"


2. How do you remove all the numbers from a cell?

eg. I only want to get the street names from a list of address:

Get only " oxford street" from "123 oxford street"
Get only "abbey road" from "2 abbey road"

Please help.