Remove letters from a cell
 

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.

Remove letters from a cell
 

On Fri, 1 Oct 2004 04:37:11 -0700, "Steve"
wrote:

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

Remove letters from a cell
 

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



"Steve" wrote in message
...
Column E of my spreadsheet contains a price. Appended to
the price is a two letter country abbreviation.

E.g
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.

Remove letters from a cell
 

Ron

Is it possible to incorporate this into a macro ?

Thanks
Steve
-----Original Message-----
On Fri, 1 Oct 2004 04:37:11 -0700, "Steve"
wrote:

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
.

Remove letters from a cell
 

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



"Steve" wrote in
message
...
Column E of my spreadsheet contains a price. Appended
to
the price is a two letter country abbreviation.

E.g
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.


.

Remove letters from a cell
 

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


--

HTH

RP

"Steve" wrote in message
...
Ron

Is it possible to incorporate this into a macro ?

Thanks
Steve
-----Original Message-----
On Fri, 1 Oct 2004 04:37:11 -0700, "Steve"
wrote:

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
.

Remove letters from a cell
 

On Fri, 1 Oct 2004 05:41:51 -0700, "Steve"
wrote:

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

Remove letters from a cell
 

On Fri, 1 Oct 2004 05:41:51 -0700, "Steve"
wrote:

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

Remove letters from a cell
 

On Fri, 1 Oct 2004 05:41:51 -0700, "Steve"
wrote:

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

Remove letters from a cell
 

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


---
Regards,
Norman



"Ron Rosenfeld" wrote in message
...
On Fri, 1 Oct 2004 05:41:51 -0700, "Steve"
wrote:

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

Remove letters from a cell
 

On Sat, 2 Oct 2004 03:52:33 +0100, "Norman Jones"
wrote:

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