Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VBA to include only a-z in end string

Posted on 2010-11-08
8
604 Views
Last Modified: 2012-05-10
Hi,
See below.
How do I remove all characters not in the range a-z or A-Z.
Essentially, I am looking for code to replace the line in bold below.

Dim sourcestring , destinationstring as string
sourcestring = "abc./ xyzABC"

**destinationstring = replace(sourcest......????
msgbox destinationstring shows   "abcxyzABC"



0
Comment
Question by:Patrick O'Dea
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 45

Accepted Solution

by:
patrickab earned 167 total points
ID: 34084979
21Dewsbury,

The code below is in the attached file.

Usage:

=bb(A1)

where A1 contains the string.

Patrick
Function bb(ByVal target As Range) As String
Dim i As Long
Dim str1 As String

For i = 1 To Len(target)
    Select Case Asc(Mid(target, i, 1))
        Case 65 To 90
           str1 = str1 & Mid(target, i, 1)
        Case 97 To 122
            str1 = str1 & Mid(target, i, 1)
    End Select
Next i

bb = str1

End Function

Open in new window

21Dewsbury-01.xls
0
 
LVL 20

Assisted Solution

by:pari123
pari123 earned 167 total points
ID: 34084996
Assuming your data is in Column C, you can use something similar to this -


            For k = 1 To Len(ws2.Range("C" & i))
                If (Asc(Mid(ws2.Range("C" & i), k, 1)) >= 65 And Asc(Mid(ws2.Range("C" & i), k, 1)) <= 90) Or _
                   (Asc(Mid(ws2.Range("C" & i), k, 1)) >= 97 And Asc(Mid(ws2.Range("C" & i), k, 1)) <= 122) Or _
                   (Asc(Mid(ws2.Range("C" & i), k, 1))) = 32 Then
                Else
                    Mid(ws2.Range("C" & i), k, 1) = ""
                End If
            Next k

Open in new window

0
 
LVL 19

Assisted Solution

by:david251
david251 earned 166 total points
ID: 34085239
you can also use this function with regular expressions like this:
Function CharactersOnly(strData As String) As String
    Dim RE As Object, REMatches As Object
    Dim strTemp As String
    strTemp = ""
    
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "[A-Za-z]+"
    End With
    
    Set REMatches = RE.Execute(strData)


    For Each i In REMatches
        strTemp = strTemp & i.Value
    Next i
    CharactersOnly = strTemp

End Function

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Patrick O'Dea
ID: 34085824
pari123,

I chose to use you code (as it suited my limited VBA ability).

However, it crashes at the line;

                    Mid(ws2.Range("C" & i), k, 1) = ""   ;

"Variable required - can't assign to this expression".

Any thought?
I guess it is saying that you can't assign a value to the middle of a string??  Is that right? What would be the syntax to simply replace the character with a "" ??





 For k = 1 To Len(ws2.Range("C" & i))
                If (Asc(Mid(ws2.Range("C" & i), k, 1)) >= 65 And Asc(Mid(ws2.Range("C" & i), k, 1)) <= 90) Or _
                   (Asc(Mid(ws2.Range("C" & i), k, 1)) >= 97 And Asc(Mid(ws2.Range("C" & i), k, 1)) <= 122) Or _
                   (Asc(Mid(ws2.Range("C" & i), k, 1))) = 32 Then
                Else
                    Mid(ws2.Range("C" & i), k, 1) = ""   
                End If
            Next k
0
 
LVL 20

Expert Comment

by:pari123
ID: 34085956
Hi Dewsbury,

Can you please try this code -

regards,
Ardhendu
Sub cleanup()
strX = ""
For i = 1 To Cells(65536, "C").End(xlUp).Row
    For k = 1 To Len(Range("C" & i))
        'MsgBox (Mid(Range("C" & i), k, 1))
        If (Asc(Mid(Range("C" & i), k, 1)) >= 65 And Asc(Mid(Range("C" & i), k, 1)) <= 90) Or _
           (Asc(Mid(Range("C" & i), k, 1)) >= 97 And Asc(Mid(Range("C" & i), k, 1)) <= 122) Then
           strX = strX & Mid(Range("C" & i), k, 1)
        End If
    Next k
    Range("C" & i) = strX
    strX = ""
Next
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:patrickab
ID: 34086019
21Dewsbury,

I would have thought that having provided the VBA code, an example and a file illustrating how it works my very simple function 'bb' would be the easiest method to adopt.

To help I have commented the code below.

Patrick


Function bb(ByVal target As Range) As String
'declare variables
Dim i As Long
Dim str1 As String

'work through the 'target' string, character by character
For i = 1 To Len(target)
'convert each character to its Ascii value
    Select Case Asc(Mid(target, i, 1))
'if the Ascii value is between 65 and 90 (A-Z) then add it to the string str1
        Case 65 To 90
           str1 = str1 & Mid(target, i, 1)
'if the Ascii value is between 97 and 122 (a-z) then add it to the string str1
        Case 97 To 122
            str1 = str1 & Mid(target, i, 1)
    End Select
Next i
'make bb equal to str1
bb = str1

End Function

Open in new window

0
 

Author Comment

by:Patrick O'Dea
ID: 34086737
Thanks Patrickab,

You code is much appreciated.Points will be shared.
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 34088268
Thanks all,

Problem solved !
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question