Hi! I have a spreadsheet that contains lots of rating info. the info look
like eg AAA, AA+, A, BBB-, Aa3 etc. now I am only intereseted in the letters
ie I want to have a worksheet function that cleans the data from the numbers
and the plus, minus signs. How can I do this? pls any help appreciated!

ASAP Utilities, a free Add-in available at www.asap-utilities.com has
features that will allow you to strip out all numerical and symbol characters
leaving only the alpha characters...

hth
Vaya con Dios,
Chuck, CABGx3



"Arne Hegefors" wrote:

Hi! I have a spreadsheet that contains lots of rating info. the info look
like eg AAA, AA+, A, BBB-, Aa3 etc. now I am only intereseted in the letters
ie I want to have a worksheet function that cleans the data from the numbers
and the plus, minus signs. How can I do this? pls any help appreciated!

Hi,

I have just come up with the following:

Paste this into a new module:

Function Stripper(Initial_Value As String)

Dim s_length As Integer
Dim c_position As Integer
Dim c_letter As String

c_position = 1
s_length = Len(Initial_Value)

Do While s_length < (c_position - 1)
c_letter = Mid(Initial_Value, c_position, 1)
If c_letter = "A" And c_letter <= "Z" Then
Stripper = Stripper & Mid(Initial_Value, c_position, 1)
End If
c_position = c_position + 1
Loop

End Function

then, on your worksheet use =stripper([cell with your value])

HTH

Thanks,

Simon

Arne Hegefors wrote:
Hi! I have a spreadsheet that contains lots of rating info. the info look
like eg AAA, AA+, A, BBB-, Aa3 etc. now I am only intereseted in the letters
ie I want to have a worksheet function that cleans the data from the numbers
and the plus, minus signs. How can I do this? pls any help appreciated!

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200610/1

If you don't want to use a helper column you can strip in place with this macro.

Public Sub StripAllButAZs()
''strips out everything except letters
Dim myRange As Range
Dim cell As Range
Dim myStr As String
Dim i As Integer

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) < 65) Or _
(Asc(UCase(Mid(myStr, i, 1))) 90) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

On Tue, 17 Oct 2006 15:12:22 GMT, "smw226 via OfficeKB.com" <u27645@uwe wrote:

Hi,

I have just come up with the following:

Paste this into a new module:

Function Stripper(Initial_Value As String)

Dim s_length As Integer
Dim c_position As Integer
Dim c_letter As String

c_position = 1
s_length = Len(Initial_Value)

Do While s_length < (c_position - 1)
c_letter = Mid(Initial_Value, c_position, 1)
If c_letter = "A" And c_letter <= "Z" Then
Stripper = Stripper & Mid(Initial_Value, c_position, 1)
End If
c_position = c_position + 1
Loop

End Function

then, on your worksheet use =stripper([cell with your value])

HTH

Thanks,

Simon

Arne Hegefors wrote:
Hi! I have a spreadsheet that contains lots of rating info. the info look
like eg AAA, AA+, A, BBB-, Aa3 etc. now I am only intereseted in the letters
ie I want to have a worksheet function that cleans the data from the numbers
and the plus, minus signs. How can I do this? pls any help appreciated!