Delete text from cell containing text and numbers?

Delete text from cell containing text and numbers?

Post by inde » Mon, 01 Dec 2003 01:03:52



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.yqcomputer.com/
~~View and post usenet messages directly from http://www.yqcomputer.com/
 
 
 

Delete text from cell containing text and numbers?

Post by Chip Pears » Mon, 01 Dec 2003 01:15:28

Try something like

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



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



containing just
search the
numbers it
Is this
http://www.yqcomputer.com/

 
 
 

Delete text from cell containing text and numbers?

Post by Gord Dibbe » Mon, 01 Dec 2003 03:04:21

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 < XXXX@XXXXX.COM >
 
 
 

Delete text from cell containing text and numbers?

Post by inde » Tue, 02 Dec 2003 02:11:18


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.yqcomputer.com/
~~View and post usenet messages directly from http://www.yqcomputer.com/
 
 
 

Delete text from cell containing text and numbers?

Post by Gord Dibbe » Tue, 02 Dec 2003 03:29:08

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 < XXXX@XXXXX.COM >
 
 
 

Delete text from cell containing text and numbers?

Post by pfsardell » Tue, 02 Dec 2003 04:42:36

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

Delete text from cell containing text and numbers?

Post by inde » Tue, 02 Dec 2003 05:31:37


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.yqcomputer.com/
~~View and post usenet messages directly from http://www.yqcomputer.com/

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

Delete text from cell containing text and numbers?

Post by Peo Sjoblo » Tue, 02 Dec 2003 06:05:47

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



creating financial statements
 
 
 

Delete text from cell containing text and numbers?

Post by Gord Dibbe » Tue, 02 Dec 2003 06:07:12

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.

Copy>Paste Special>Values>OK 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 < XXXX@XXXXX.COM >
 
 
 

Delete text from cell containing text and numbers?

Post by inde » Tue, 02 Dec 2003 06:48:10


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


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

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