Remove letters from a cell

[Steve]

Column E of my spreadsheet contains a price. Appended to
the price is a two letter country abbreviation.

E.g 3.50GB
or 2.67EU

I would like to remove any letters but leave the numbers.

One further point to note, trimming the column will not
work as some numbers do not have a country denomination.

 

[Ron Rosenfeld]

Column E of my spreadsheet contains a price. Appended to
the price is a two letter country abbreviation.

E.g 3.50GB
or 2.67EU

I would like to remove any letters but leave the numbers.

One further point to note, trimming the column will not
work as some numbers do not have a country denomination.

=IF(ISNUMBER(--TRIM(A1)),--A1,--LEFT(TRIM(A1),LEN(TRIM(A1))-2))


--ron

 

[Norman Jones]

Hi Steve,

Assume your alphanumeric prices are in cells E2:E100

Sub Tester()
Dim sStr As String
Dim Rng As Range, RCell As Range

Set Rng = Range("E2:E100") '<<<===== CHANGE

For Each RCell In Rng
sStr = RCell.Value
If Not IsEmpty(RCell) Then
If Not IsNumeric(RCell) Then
RCell.Value = CDbl(Left(sStr, Len(sStr) - 2))
End If
End If
Next

End Sub

 

[Steve]

Ron

Is it possible to incorporate this into a macro ?

Thanks
Steve

 

[Steve]

Thanks Norman

-----Original Message-----
Hi Steve,

Assume your alphanumeric prices are in cells E2:E100

Sub Tester()
Dim sStr As String
Dim Rng As Range, RCell As Range

Set Rng = Range("E2:E100") '<<<===== CHANGE

For Each RCell In Rng
sStr = RCell.Value
If Not IsEmpty(RCell) Then
If Not IsNumeric(RCell) Then
RCell.Value = CDbl(Left(sStr, Len(sStr) - 2))
End If
End If
Next

End Sub

---
Regards,
Norman



denomination.


.

 

[Bob Phillips]

If IsNumeric(Range("A1")) Then
myVar = Range("A1")
Else
myVar = Left(Trim(Range("A1")), Len(Trim(Range("A1"))) - 2)
End If

 

[Ron Rosenfeld]

Ron

Is it possible to incorporate this into a macro ?

Thanks
Steve

As a macro, it can be even simpler, since the beginning part is always a
number.

===================

--ron

 

[Ron Rosenfeld]

Ron

Is it possible to incorporate this into a macro ?

Thanks
Steve

Oops.

As a macro, even simpler, since the beginning part is always a number:

===============
Function GetNum(rg As String) As Double
GetNum = Val(rg)
End Function
==============
--ron

 

[Ron Rosenfeld]

Ron

Is it possible to incorporate this into a macro ?

Thanks

Or, as a SUB to change everything in a selected range:

===============
Sub GetNum()
Dim c As Range
For Each c In Selection
If Val(c) <> 0 Then c = Val(c)
Next c
End Sub
===============


--ron

 

[Norman Jones]

Hi Ron,

I like your Val idea, but would your sub not destroy formulae?

Perhaps the following adaptation might be safer:

Sub GetNum()
Dim c As Range
Dim rng As Range

On Error Resume Next
Set rng = Selection.SpecialCells _
(xlConstants, xlTextValues)
On Error GoTo 0
If Not rng Is Nothing Then
For Each c In rng
If Val(c) <> 0 Then c = Val(c)
Next c
End If
End Sub

 

[Ron Rosenfeld]

I like your Val idea, but would your sub not destroy formulae?

Yes, the SUB would destroy formulas.

But, not being certain of the setup or desires of the OP, I posted both a UDF
and a SUB.

I suppose another method of writing the sub, and still getting a similar
result, would be something like:

====================
Sub GetNum()
Dim c As Range
For Each c In Selection
If Val(c) <> 0 Then c.offset(0,n) = Val(c)
Next c
End Sub
==================

where n is the offset to the column desired for the result.


--ron