Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using TEXT and &TEXT - display numbers with commas, underline text | Excel Discussion (Misc queries) | |||
Removal of text from a cell containing both text and numbers | Excel Discussion (Misc queries) | |||
Removing text from a cell with text and numbers | Excel Discussion (Misc queries) | |||
Easiest way to remove text from a cell that has text and numbers? | Excel Discussion (Misc queries) | |||
Delete text while numbers remain intact | Excel Worksheet Functions |