Text Cleaning

Text Cleaning

Post by sonal gupt » Wed, 06 Jun 2012 00:14:34



Hi,

This is my first post in this group.I have learnt many things from this
group.I'm finding difficulty in cleaning the range in which each cell has
string like " rahul;6e3-".I need a macro which will clean the special
characters,spaces & numbers & leave the alphabets only.

Thanks in advance.

 
 
 

Text Cleaning

Post by ashish kou » Wed, 06 Jun 2012 00:21:06


Sub text_clean()
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Dim cell As Range
Dim str As String, text1 As String
For Each cell In Selection
str = ""
text1 = ""
str = cell.Text
For i = 1 To Len(str)
If (Asc(Mid(str, i, 1)) > 64 And Asc(Mid(str, i, 1)) < 90) Or (Asc(Mid(str,
i, 1)) > 96 And Asc(Mid(str, i, 1)) < 123) Then
text1 = text1 & Mid(str, i, 1)
End If
Next i
cell.Value = text1
Next
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub


> Hi,

> This is my first post in this group.I have learnt many things from this
> group.I'm finding difficulty in cleaning the range in which each cell has
> string like " rahul;6e3-".I need a macro which will clean the special
> characters,spaces & numbers & leave the alphabets only.

> Thanks in advance.

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


--
*Regards*
* *
*Ashish Koul*
*http://www.excelvbamacros.com/*
*http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

P Before printing, think about the environment.

 
 
 

Text Cleaning

Post by Rajan_Verm » Wed, 06 Jun 2012 00:21:52


Try this :

Public Function RemoveSpecialCharacters(Shname As String) As String

Dim Cod As Integer

Dim ShN As String

            For i = 1 To Len(Shname)

                Cod = Asc(Mid(Shname, i, 1))

                        If (Cod > 47 And Cod < 58) Or (Cod > 64 And Cod <
91) Or (Cod > 96 And Cod < 123) Then

                        ShN = ShN & Mid(Shname, i, 1)

                        End If

            Next

            RemoveSpecialCharacters = ShN

End Function

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of sonal gupta
Sent: 04 June 2012 8:45

Subject: $$Excel-Macros$$ Text Cleaning

Hi,

This is my first post in this group.I have learnt many things from this
group.I'm finding difficulty in cleaning the range in which each cell has
string like " rahul;6e3-".I need a macro which will clean the special
characters,spaces & numbers & leave the alphabets only.

Thanks in advance.

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Text Cleaning

Post by sonal gupt » Wed, 06 Jun 2012 00:28:01


Thanks rajan, but i'm new to macros..can you please explain the use of
"COD" in this.


>  Try this :

> Public Function RemoveSpecialCharacters(Shname As String) As String

> Dim Cod As Integer

> Dim ShN As String

>             For i = 1 To Len(Shname)

>                 Cod = Asc(Mid(Shname, i, 1))

>                         If (Cod > 47 And Cod < 58) Or (Cod > 64 And Cod <
> 91) Or (Cod > 96 And Cod < 123) Then

>                         ShN = ShN & Mid(Shname, i, 1)

>                         End If

>             Next

>             RemoveSpecialCharacters = ShN

> End Function

> * *

> *Regards*

> *Rajan verma*

> *+91 7838100659 [IM-Gtalk]*



> *Sent:* 04 June 2012 8:45

> *Subject:* $$Excel-Macros$$ Text Cleaning

> Hi,

> This is my first post in this group.I have learnt many things from this
> group.I'm finding difficulty in cleaning the range in which each cell has
> string like " rahul;6e3-".I need a macro which will clean the special
> characters,spaces & numbers & leave the alphabets only.

> Thanks in advance.

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


 
 
 

Text Cleaning

Post by sonal gupt » Wed, 06 Jun 2012 00:28:41


Thanks ashish.I'll try it.


> Sub text_clean()
> Application.DisplayAlerts = False
> Application.Calculation = xlCalculationManual
> Dim cell As Range
> Dim str As String, text1 As String
> For Each cell In Selection
> str = ""
> text1 = ""
> str = cell.Text
> For i = 1 To Len(str)
> If (Asc(Mid(str, i, 1)) > 64 And Asc(Mid(str, i, 1)) < 90) Or
> (Asc(Mid(str, i, 1)) > 96 And Asc(Mid(str, i, 1)) < 123) Then
> text1 = text1 & Mid(str, i, 1)
> End If
> Next i
> cell.Value = text1
> Next
> Application.DisplayAlerts = True
> Application.Calculation = xlCalculationAutomatic
> End Sub


>> Hi,

>> This is my first post in this group.I have learnt many things from this
>> group.I'm finding difficulty in cleaning the range in which each cell has
>> string like " rahul;6e3-".I need a macro which will clean the special
>> characters,spaces & numbers & leave the alphabets only.

>> Thanks in advance.

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

> --
> *Regards*
> * *
> *Ashish Koul*
> *http://www.excelvbamacros.com/*
> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

> P Before printing, think about the environment.

 
 
 

Text Cleaning

Post by Rajan_Verm » Wed, 06 Jun 2012 00:29:23


Ok..

Press ALT+F11

ALT + I M

Paste this code there

Use Function Worksheet Like :

= RemoveSpecialCharacters(YourString)

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of sonal gupta
Sent: 04 June 2012 8:58

Subject: Re: $$Excel-Macros$$ Text Cleaning

Thanks rajan, but i'm new to macros..can you please explain the use of "COD"
in this.

Try this :

Public Function RemoveSpecialCharacters(Shname As String) As String

Dim Cod As Integer

Dim ShN As String

            For i = 1 To Len(Shname)

                Cod = Asc(Mid(Shname, i, 1))

                        If (Cod > 47 And Cod < 58) Or (Cod > 64 And Cod <
91) Or (Cod > 96 And Cod < 123) Then

                        ShN = ShN & Mid(Shname, i, 1)

                        End If

            Next

            RemoveSpecialCharacters = ShN

End Function

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of sonal gupta
Sent: 04 June 2012 8:45

Subject: $$Excel-Macros$$ Text Cleaning

Hi,

This is my first post in this group.I have learnt many things from this
group.I'm finding difficulty in cleaning the range in which each cell has
string like " rahul;6e3-".I need a macro which will clean the special
characters,spaces & numbers & leave the alphabets only.

Thanks in advance.

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Text Cleaning

Post by ashish kou » Wed, 06 Jun 2012 00:29:20


select the range on the worksheet and run the macro


> Thanks ashish.I'll try it.


>> Sub text_clean()
>> Application.DisplayAlerts = False
>> Application.Calculation = xlCalculationManual
>> Dim cell As Range
>> Dim str As String, text1 As String
>> For Each cell In Selection
>> str = ""
>> text1 = ""
>> str = cell.Text
>> For i = 1 To Len(str)
>> If (Asc(Mid(str, i, 1)) > 64 And Asc(Mid(str, i, 1)) < 90) Or
>> (Asc(Mid(str, i, 1)) > 96 And Asc(Mid(str, i, 1)) < 123) Then
>> text1 = text1 & Mid(str, i, 1)
>> End If
>> Next i
>> cell.Value = text1
>> Next
>> Application.DisplayAlerts = True
>> Application.Calculation = xlCalculationAutomatic
>> End Sub


>>> Hi,

>>> This is my first post in this group.I have learnt many things from this
>>> group.I'm finding difficulty in cleaning the range in which each cell has
>>> string like " rahul;6e3-".I need a macro which will clean the special
>>> characters,spaces & numbers & leave the alphabets only.

>>> Thanks in advance.

>>> --
>>> FORUM RULES (986+ members already BANNED for violation)

>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.

>>> 2) Don't post a question in the thread of another member.

>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.

>>> 4) Acknowledge the responses you receive, good or bad.

>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.

>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.

>>> ------------------------------**------------------------------**
>>> ------------------------------**------------



>> --
>> *Regards*
>> * *
>> *Ashish Koul*
>> *http://www.excelvbamacros.com/*
>> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

>> P Before printing, think about the environment.

>>  --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


--
*Regards*
* *
*Ashish Koul*
*http://www.excelvbamacros.com/*
*http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

P Before printing, think about the environment.

 
 
 

Text Cleaning

Post by sona » Wed, 06 Jun 2012 00:34:22


again thanks but i know how to run a macro what i was asking is
the explanation for "COD" that ypu have used  in your code.


>  Ok..

> Press ALT+F11

> ALT + I M

> Paste this code there

> Use Function Worksheet Like :

> = RemoveSpecialCharacters(YourString)

> * *

> *Regards*

> *Rajan verma*

> *+91 7838100659 [IM-Gtalk]*



> *Sent:* 04 June 2012 8:58

> *Subject:* Re: $$Excel-Macros$$ Text Cleaning

> Thanks rajan, but i'm new to macros..can you please explain the use of
> "COD" in this.


>  Try this :

> Public Function RemoveSpecialCharacters(Shname As String) As String

> Dim Cod As Integer

> Dim ShN As String

>             For i = 1 To Len(Shname)

>                 Cod = Asc(Mid(Shname, i, 1))

>                         If (Cod > 47 And Cod < 58) Or (Cod > 64 And Cod <
> 91) Or (Cod > 96 And Cod < 123) Then

>                         ShN = ShN & Mid(Shname, i, 1)

>                         End If

>             Next

>             RemoveSpecialCharacters = ShN

> End Function

> * *

> *Regards*

> *Rajan verma*

> *+91 7838100659 [IM-Gtalk]*



> *Sent:* 04 June 2012 8:45

> *Subject:* $$Excel-Macros$$ Text Cleaning

> Hi,

> This is my first post in this group.I have learnt many things from this
> group.I'm finding difficulty in cleaning the range in which each cell has
> string like " rahul;6e3-".I need a macro which will clean the special
> characters,spaces & numbers & leave the alphabets only.

> Thanks in advance.

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


 
 
 

Text Cleaning

Post by Asa Rossof » Wed, 06 Jun 2012 00:38:01


Hi Sonal,

Another function for you:

Function CleanText(Text As String) As String

Dim NewText As String, Character As String * 1, Position As Long

    For Position = 1 To Len(Text)

        Character = Mid(Text, Position, 1)

        If Character Like "[A-z]" Or Character Like "#" Then

            NewText = NewText & Character

        End If

    Next Position

    CleanText = NewText

End Function

You can use this as a UDF in a worksheet formula or call it from other
macros.

Asa


On Behalf Of sonal gupta
Sent: Monday, June 04, 2012 8:15 AM

Subject: $$Excel-Macros$$ Text Cleaning

Hi,

This is my first post in this group.I have learnt many things from this
group.I'm finding difficulty in cleaning the range in which each cell has
string like " rahul;6e3-".I need a macro which will clean the special
characters,spaces & numbers & leave the alphabets only.

Thanks in advance.

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Text Cleaning

Post by Rajan_Verm » Wed, 06 Jun 2012 00:41:22


Ah , Cod is just a integer type variable , that will hold the ACSII Code or
each character in string,

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of sonal
Sent: 04 June 2012 9:04

Subject: Re: $$Excel-Macros$$ Text Cleaning

again thanks but i know how to run a macro what i was asking is the
explanation for "COD" that ypu have used  in your code.

Ok..

Press ALT+F11

ALT + I M

Paste this code there

Use Function Worksheet Like :

= RemoveSpecialCharacters(YourString)

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of sonal gupta
Sent: 04 June 2012 8:58

Subject: Re: $$Excel-Macros$$ Text Cleaning

Thanks rajan, but i'm new to macros..can you please explain the use of "COD"
in this.

Try this :

Public Function RemoveSpecialCharacters(Shname As String) As String

Dim Cod As Integer

Dim ShN As String

            For i = 1 To Len(Shname)

                Cod = Asc(Mid(Shname, i, 1))

                        If (Cod > 47 And Cod < 58) Or (Cod > 64 And Cod <
91) Or (Cod > 96 And Cod < 123) Then

                        ShN = ShN & Mid(Shname, i, 1)

                        End If

            Next

            RemoveSpecialCharacters = ShN

End Function

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of sonal gupta
Sent: 04 June 2012 8:45

Subject: $$Excel-Macros$$ Text Cleaning

Hi,

This is my first post in this group.I have learnt many things from this
group.I'm finding difficulty in cleaning the range in which each cell has
string like " rahul;6e3-".I need a macro which will clean the special
characters,spaces & numbers & leave the alphabets only.

Thanks in advance.

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Text Cleaning

Post by Asa Rossof » Wed, 06 Jun 2012 00:44:07


I re-read your query and I see you only wanted alphabet characters.  I had
this include numbers as well. for just alphabetical characters A-Z and a-z,
here is the correction:

Function CleanText(Text As String) As String

Dim NewText As String, Character As String * 1, Position As Long

    For Position = 1 To Len(Text)

        Character = Mid(Text, Position, 1)

        If Character Like "[A-z]" Then

            NewText = NewText & Character

        End If

    Next Position

    CleanText = NewText

End Function

By the way, welcome to the group!

Asa


On Behalf Of Asa Rossoff
Sent: Monday, June 04, 2012 8:38 AM

Subject: RE: $$Excel-Macros$$ Text Cleaning

Hi Sonal,

Another function for you:

Function CleanText(Text As String) As String

Dim NewText As String, Character As String * 1, Position As Long

    For Position = 1 To Len(Text)

        Character = Mid(Text, Position, 1)

        If Character Like "[A-z]" Or Character Like "#" Then

            NewText = NewText & Character

        End If

    Next Position

    CleanText = NewText

End Function

You can use this as a UDF in a worksheet formula or call it from other
macros.

Asa


On Behalf Of sonal gupta
Sent: Monday, June 04, 2012 8:15 AM

Subject: $$Excel-Macros$$ Text Cleaning

Hi,

This is my first post in this group.I have learnt many things from this
group.I'm finding difficulty in cleaning the range in which each cell has
string like " rahul;6e3-".I need a macro which will clean the special
characters,spaces & numbers & leave the alphabets only.

Thanks in advance.

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Text Cleaning

Post by David Grugeo » Wed, 06 Jun 2012 09:41:41


Hi Sonal

I hope you realise that if you apply any of the suggested solutions to your
string " rahul;6e3-" you will get "rahule" not "rahul".  I hope this is
what you want.  If not we might need to see a greater selection of strings
to see if there is a pattern (like - always cut them off at the first
 non-letter).

Regards
David Grugeon


> Thanks ashish.I'll try it.


>> Sub text_clean()
>> Application.DisplayAlerts = False
>> Application.Calculation = xlCalculationManual
>> Dim cell As Range
>> Dim str As String, text1 As String
>> For Each cell In Selection
>> str = ""
>> text1 = ""
>> str = cell.Text
>> For i = 1 To Len(str)
>> If (Asc(Mid(str, i, 1)) > 64 And Asc(Mid(str, i, 1)) < 90) Or
>> (Asc(Mid(str, i, 1)) > 96 And Asc(Mid(str, i, 1)) < 123) Then
>> text1 = text1 & Mid(str, i, 1)
>> End If
>> Next i
>> cell.Value = text1
>> Next
>> Application.DisplayAlerts = True
>> Application.Calculation = xlCalculationAutomatic
>> End Sub


>>> Hi,

>>> This is my first post in this group.I have learnt many things from this
>>> group.I'm finding difficulty in cleaning the range in which each cell has
>>> string like " rahul;6e3-".I need a macro which will clean the special
>>> characters,spaces & numbers & leave the alphabets only.

>>> Thanks in advance.

>>> --
>>> FORUM RULES (986+ members already BANNED for violation)

>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.

>>> 2) Don't post a question in the thread of another member.

>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.

>>> 4) Acknowledge the responses you receive, good or bad.

>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.

>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.

>>> ------------------------------**------------------------------**
>>> ------------------------------**------------



>> --
>> *Regards*
>> * *
>> *Ashish Koul*
>> *http://www.excelvbamacros.com/*
>> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

>> P Before printing, think about the environment.

>>  --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


--
David Grugeon
 
 
 

Text Cleaning

Post by SG » Wed, 06 Jun 2012 18:33:57


Hi Asa,

your code really works exact for me.Thanks for the help but can u please
explain why you have done this "Character As String * 1".


>  I re-read your query and I see you only wanted alphabet characters.  I
> had this include numbers as well for just alphabetical characters A-Z and
> a-z, here is the correction:

> Function CleanText(Text As String) As String

> Dim NewText As String, Character As String * 1, Position As Long

>     For Position = 1 To Len(Text)

>         Character = Mid(Text, Position, 1)

>         If Character Like "[A-z]" Then

>             NewText = NewText & Character

>         End If

>     Next Position

>     CleanText = NewText

> End Function

> By the way, welcome to the group!

> Asa



> *Sent:* Monday, June 04, 2012 8:38 AM

> *Subject:* RE: $$Excel-Macros$$ Text Cleaning

> Hi Sonal,

> Another function for you:

> Function CleanText(Text As String) As String

> Dim NewText As String, Character As String * 1, Position As Long

>     For Position = 1 To Len(Text)

>         Character = Mid(Text, Position, 1)

>         If Character Like "[A-z]" Or Character Like "#" Then

>             NewText = NewText & Character

>         End If

>     Next Position

>     CleanText = NewText

> End Function

> You can use this as a UDF in a worksheet formula or call it from other
> macros.

> Asa



> Behalf Of *sonal gupta
> *Sent:* Monday, June 04, 2012 8:15 AM

> *Subject:* $$Excel-Macros$$ Text Cleaning

> Hi,

> This is my first post in this group.I have learnt many things from this
> group.I'm finding difficulty in cleaning the range in which each cell has
> string like " rahul;6e3-".I need a macro which will clean the special
> characters,spaces & numbers & leave the alphabets only.

> Thanks in advance.

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


 
 
 

Text Cleaning

Post by SG » Wed, 06 Jun 2012 18:35:43


Hi david.
that "e" was bymistake.I want to clean special characters & spaces & it's
done by Asa's code.

thanks for your concern.


> Hi Sonal

> I hope you realise that if you apply any of the suggested solutions to
> your string " rahul;6e3-" you will get "rahule" not "rahul".  I hope this
> is what you want.  If not we might need to see a greater selection of
> strings to see if there is a pattern (like - always cut them off at the
> first non-letter).

> Regards
> David Grugeon


>> Thanks ashish.I'll try it.


>>> Sub text_clean()
>>> Application.DisplayAlerts = False
>>> Application.Calculation = xlCalculationManual
>>> Dim cell As Range
>>> Dim str As String, text1 As String
>>> For Each cell In Selection
>>> str = ""
>>> text1 = ""
>>> str = cell.Text
>>> For i = 1 To Len(str)
>>> If (Asc(Mid(str, i, 1)) > 64 And Asc(Mid(str, i, 1)) < 90) Or
>>> (Asc(Mid(str, i, 1)) > 96 And Asc(Mid(str, i, 1)) < 123) Then
>>> text1 = text1 & Mid(str, i, 1)
>>> End If
>>> Next i
>>> cell.Value = text1
>>> Next
>>> Application.DisplayAlerts = True
>>> Application.Calculation = xlCalculationAutomatic
>>> End Sub


>>>> Hi,

>>>> This is my first post in this group.I have learnt many things from this
>>>> group.I'm finding difficulty in cleaning the range in which each cell has
>>>> string like " rahul;6e3-".I need a macro which will clean the special
>>>> characters,spaces & numbers & leave the alphabets only.

>>>> Thanks in advance.

>>>> --
>>>> FORUM RULES (986+ members already BANNED for violation)

>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.

>>>> 2) Don't post a question in the thread of another member.

>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.

>>>> 4) Acknowledge the responses you receive, good or bad.

>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.

>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.

>>>> ------------------------------**------------------------------**
>>>> ------------------------------**------------



>>> --
>>> *Regards*
>>> * *
>>> *Ashish Koul*
>>> *http://www.excelvbamacros.com/*
>>> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

>>> P Before printing, think about the environment.

>>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

> --
> David Grugeon

 
 
 

1. Possible to get clean outlines in PDF text viewed online?

We are trying to decide whether Adobe's products are the ones we want to use
for publishing our technical manuals online.

Most  (if not all) PDF documents I've viewed online have fuzzy edges around
the text characters rather than clean sharp outlines. If I magnify the type
greatly, I can see the reason for this fuzzy appearance: the edges or the
characters are not black, but instead they are feathered in shades of grey.

Apparently Adobe is happy with this result because even the PDFs on their own
Web site have this fuzzy type when viewed online.

Has anyone found a way of getting clean edges on their PDF typefaces for
online viewing --- and if so, could you share your secrets?

Thanks,
Mike West

2. Quantum 4.3GB Drive in a Indigo2 > DMA errors ???

3. Getting a 'clean' text download from a Quark file

4. SoundBlaster SCSI-2 Card?

5. Help: Converting postscript to clean text

6. <@ Page CodeBehind=...???

7. Cleaning up text

8. URGENT HELP NEEDED : on matlab function conv.m

9. Cleaning DOS break chars and text wrapping in FTE?

10. Cleaning up a TEXT WIDGET

11. Clean up variables/conditional text in Frame 6/7

12. Which program to transform a scanned text-page in a text ?

13. HP6200...Whats the smallest format to save text images(not using text for.)??