Delete text from cell containing text and numbers?
 

Hi,


A list of product codes that i use includes some cells containing jus
numbers and other cells containing numbers and text.

Does anyone know how to create a macro that when run will search th
selection and when it finds a cell containing both text and numbers i
will remove the text from the cell leaving the numbers behind? Is thi
possible?

Any help would be much appreciate

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Delete text from cell containing text and numbers?
 

Try something like

Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Delete shift:=xlUp



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"index" wrote in message
...

Hi,


A list of product codes that i use includes some cells
containing just
numbers and other cells containing numbers and text.

Does anyone know how to create a macro that when run will
search the
selection and when it finds a cell containing both text and
numbers it
will remove the text from the cell leaving the numbers behind?
Is this
possible?

Any help would be much appreciated


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

Delete text from cell containing text and numbers?
 

Public Sub StripAllAZs()
''strips out everything except numbers
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))) < 48) Or _
(Asc(UCase(Mid(myStr, i, 1))) 57) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Gord Dibben XL2002

On Sat, 29 Nov 2003 10:03:52 -0600, index
wrote:


Hi,


A list of product codes that i use includes some cells containing just
numbers and other cells containing numbers and text.

Does anyone know how to create a macro that when run will search the
selection and when it finds a cell containing both text and numbers it
will remove the text from the cell leaving the numbers behind? Is this
possible?

Any help would be much appreciated


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Delete text from cell containing text and numbers?
 

Thanks for the help, however a small part of it is not working...

When I run it the lines
"Selection.Replace What:=" ", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I'm being told there is either a Expected named parameter or a Synta
error. Can anyone help me fix this please??

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Delete text from cell containing text and numbers?
 

You may be the victim of line-wrapping.

Try....

"Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Or

"Selection.Replace What:=" ", Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

The <sp_ is a line-continuation character. The code is part of one
continuous line.

Gord

On Sun, 30 Nov 2003 11:11:18 -0600, index
wrote:


Thanks for the help, however a small part of it is not working...

When I run it the lines
"Selection.Replace What:=" ", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I'm being told there is either a Expected named parameter or a Syntax
error. Can anyone help me fix this please???


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Delete text from cell containing text and numbers?
 

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Hi,


A list of product codes that i use includes some cells containing just
numbers and other cells containing numbers and text.

Does anyone know how to create a macro that when run will search the
selection and when it finds a cell containing both text and numbers it
will remove the text from the cell leaving the numbers behind? Is this
possible?

Any help would be much appreciated

Delete text from cell containing text and numbers?
 

Thanks very much for your help. However, although i am no longer gettin
any error messages, the macro is not doing anything to my data!

I am trying to remove any text from a cell, ie

abc123
123abc
ab123c

becomes...

123
123
123

Any further help would be much appreciated (apologies if this is
simple task being made difficult by a simple user!!!

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Delete text from cell containing text and numbers?
 

Both Gord and Paul's macros work for me.

Here a formula solution that would work for your examples (only one
occurrence of numbers)

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter, copy down and later copy and paste
special as values in place

However this is much easier to do using VBA and both the solutions you
received from Gord and Paul works
for me

--

Regards,

Peo Sjoblom

"index" wrote in message
...

Thanks very much for your help. However, although i am no longer getting
any error messages, the macro is not doing anything to my data!

I am trying to remove any text from a cell, ie

abc123
123abc
ab123c

becomes...

123
123
123

Any further help would be much appreciated (apologies if this is a
simple task being made difficult by a simple user!!!)


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Delete text from cell containing text and numbers?
 

Index

Don't know who you are responding to. You have a couple of suggestions from
myself and at least one other.

If myself....code works fine for me.

Select the cell(s) with abc123 and 123abc then run the macro.

Is it possible your text is generated via formula? Like =A1 & A2 or similar?
If so, the code will not work.

CopyPaste SpecialValuesOK would get rid of any formulas.

Try the code that Paul provided. It also works for me.

Gord

On Sun, 30 Nov 2003 14:31:37 -0600, index
wrote:


Thanks very much for your help. However, although i am no longer getting
any error messages, the macro is not doing anything to my data!

I am trying to remove any text from a cell, ie

abc123
123abc
ab123c

becomes...

123
123
123

Any further help would be much appreciated (apologies if this is a
simple task being made difficult by a simple user!!!)


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Delete text from cell containing text and numbers?
 

Thanks everyone, got in working now. Your help is much appreciated


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements