Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going through a string, a char at a time.
Hello is it possible to go through a String checking for certain characters?
Basically want to somehow convert a String to a character array and go through it checking for the ' character. Or failing that, is there a function that disposes of certain characters within a string. Cheers. Nate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going through a string, a char at a time.
Hi,
It's not very pretty but it works for me; Sub removechar() Dim i, l As Integer Dim mystr1, mystr2 As String mystr1 = "how to remove ' from a string" l = Len(mystr1) For i = 1 To l If Mid(mystr1, i, 1) < "'" Then mystr2 = mystr2 & Mid(mystr1, i, 1) End If Next i End Sub Ammend to sub or UDf as neccessary. Hopefully someone else will have a sexier solution! "NateBuckley" wrote: Hello is it possible to go through a String checking for certain characters? Basically want to somehow convert a String to a character array and go through it checking for the ' character. Or failing that, is there a function that disposes of certain characters within a string. Cheers. Nate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going through a string, a char at a time.
Hi,
You can remove characters from a string by using the replace function Msgbox Replace("checking for the ' character","'","") Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "NateBuckley" wrote in message ... Hello is it possible to go through a String checking for certain characters? Basically want to somehow convert a String to a character array and go through it checking for the ' character. Or failing that, is there a function that disposes of certain characters within a string. Cheers. Nate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going through a string, a char at a time.
Hehe, well It may not be sexy but it gets the job done.
Thank yee kindly good sir/madam. (Circle which applies). "Flower" wrote: Hi, It's not very pretty but it works for me; Sub removechar() Dim i, l As Integer Dim mystr1, mystr2 As String mystr1 = "how to remove ' from a string" l = Len(mystr1) For i = 1 To l If Mid(mystr1, i, 1) < "'" Then mystr2 = mystr2 & Mid(mystr1, i, 1) End If Next i End Sub Ammend to sub or UDf as neccessary. Hopefully someone else will have a sexier solution! "NateBuckley" wrote: Hello is it possible to go through a String checking for certain characters? Basically want to somehow convert a String to a character array and go through it checking for the ' character. Or failing that, is there a function that disposes of certain characters within a string. Cheers. Nate |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going through a string, a char at a time.
This may not be what you are looking for but to remove a letter, (or
character if not a letter), from a cell try: =SUBSTITUTE(A1,"e","") To count the number of occurrences of the character use: =LEN(A1)-LEN(SUBSTITUTE(A1,"e","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "NateBuckley" wrote in message ... Hello is it possible to go through a String checking for certain characters? Basically want to somehow convert a String to a character array and go through it checking for the ' character. Or failing that, is there a function that disposes of certain characters within a string. Cheers. Nate |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going through a string, a char at a time.
A "sexier" subroutine might be...
Sub RemoveApostrophe() Dim mystr1 As String, mystr2 As String mystr1 = "how to remove ' from a string" mystr2 = Replace(mystr1, "'", "") End Sub By the way, note the difference between my Dim statement for mystr1 and mystr2 and yours. Leaving out the As String declaration for mystr1 make it a Variant, not a String variable. In VB(A), all variables must be declared as to their type or else they get declared as a default of Variant (this differs from other languages you may be familiar with). Rick "Flower" wrote in message ... Hi, It's not very pretty but it works for me; Sub removechar() Dim i, l As Integer Dim mystr1, mystr2 As String mystr1 = "how to remove ' from a string" l = Len(mystr1) For i = 1 To l If Mid(mystr1, i, 1) < "'" Then mystr2 = mystr2 & Mid(mystr1, i, 1) End If Next i End Sub Ammend to sub or UDf as neccessary. Hopefully someone else will have a sexier solution! "NateBuckley" wrote: Hello is it possible to go through a String checking for certain characters? Basically want to somehow convert a String to a character array and go through it checking for the ' character. Or failing that, is there a function that disposes of certain characters within a string. Cheers. Nate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to retrieve certain char within a string? | Excel Discussion (Misc queries) | |||
How do i get the last position of a char (space) in a string? | Excel Worksheet Functions | |||
sql string, +'/'+convert(char(3)..) | Excel Programming | |||
string/char CONTAINS function? | Excel Worksheet Functions | |||
last char of a string | Excel Programming |