Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Extract numbers from cell with Text and Numbers | New Users to Excel | |||
remove text from cell containing numbers | Excel Discussion (Misc queries) | |||
How can I remove diff. numbers from a cell combined with text | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |