Easiest way to remove text from a cell that has text and numbers?
Hi,
I'm just trying to figure out the easiest way to just pull the text out of cells that have both text and numbers to leave just the number. (the letters are indiscriminately placed in the cell so I can't just chop off the beginning or end). I could do multiple incarnations of Selection.Replace like this: Selection.Replace What:="A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="B", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="C", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="D", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False etc. to Z But I thought I'd ask to see if someone could provide me with a more elegant solution (like using an array). I googled for a better solution but to no avail. tia |
Easiest way to remove text from a cell that has text and numbers?
Sub RemoveAlphas()
' Remove alpha characters from a string. ' except for decimal points ' if don't want decimals, delete the decimal pt. from "[0-9.]" befroe running 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 Gord Dibben MS Excel MVP On 17 Aug 2006 08:31:11 -0700, wrote: Hi, I'm just trying to figure out the easiest way to just pull the text out of cells that have both text and numbers to leave just the number. (the letters are indiscriminately placed in the cell so I can't just chop off the beginning or end). I could do multiple incarnations of Selection.Replace like this: Selection.Replace What:="A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="B", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="C", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="D", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False etc. to Z But I thought I'd ask to see if someone could provide me with a more elegant solution (like using an array). I googled for a better solution but to no avail. tia |
All times are GMT +1. The time now is 09:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com