Removing letters in graph data

[jdobres]

I make a lot of graphs at my job. The data is given to me, sometimes with letters next to the data (i.e., 34ABC). I then have to go an manually delete the letters from all the numbers. Is there a way to do this with a formula? I have excel 97. Find and Replace doesn't have all the options like Word does.
Thanks.

[MT_Shanachie]

On 2002-05-03 11:06, jdobres wrote:
I make a lot of graphs at my job. The data is given to me, sometimes with letters next to the data (i.e., 34ABC). I then have to go an manually delete the letters from all the numbers. Is there a way to do this with a formula? I have excel 97. Find and Replace doesn't have all the options like Word does.
Thanks.

I can't think of an easy way to do this unless there's some pattern to the data. For example, is the first non-numeric character always an "A", or are there always a certain number of numeric digits before the letters?

[jdobres]

It's always random. Usually it's a two didgit number, but the letters are pretty much random in their order.

[MT_Shanachie]

If it's always a 2 digit number before the letters, you could use =value(left(a1,2)) to pull the leftmost two digits out of the string. If it's not consistently 2 digits, try this. Put the following code in a module in the same workbook as your data:

Function RemoveLetters(InputText As String) As Long
Dim n As Integer
Dim NewVal As String
NewVal = ""
For n = 1 To Len(InputText)
If IsNumeric(Mid(InputText, n, 1)) Then
NewVal = NewVal & Mid(InputText, n, 1)
End If
Next n
RemoveLetters = Val(NewVal)
End Function

You can then use the RemoveLetters function like you would any worksheet function. For example, "34ABC" is in cell A1, in A2 type =RemoveLetters(A1). You can then copy and Paste Special | Values to get rid of the formulas.

I hope this gets you somewhere.