Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do you extract numbers from a string of chacters in a cell (E.
How do you extract numbers or dates from a string of characters in a cell?
I have a string of characters. My dog has 3330 fleas. I want to place the number in its own cell. |
#2
|
|||
|
|||
Hi
do you only have one occurence of strings in your cell? If yes you may try the following array formula (entered with CTRL+SHIFT+ENTER: =--MID(A1,MIN(IF(ISNUMBER(-MID(A1,seq,1)),seq)),LOOKUP(2,1/ISNUMBER(-MID(A1,seq,1)),seq)-MIN(IF(ISNUMBER(-MID(A1,seq,1)),seq))+1) where seq is a defined name with the formula: seq: =ROW(INDIRECT("1:1024")) "blackbeemer" wrote: How do you extract numbers or dates from a string of characters in a cell? I have a string of characters. My dog has 3330 fleas. I want to place the number in its own cell. |
#3
|
|||
|
|||
=--REPLACE(LEFT(A2,MAX(FIND(0,SUBSTITUTE(A2,{1,2,3,4, 5,6,7,8,9},0)&0)+1)),1,MIN(FIND(0,SUBSTITUTE(A2,{1 ,2,3,4,5,6,7,8,9},0)&0)-1),"") where A2 houses a target string with a number. blackbeemer Wrote: How do you extract numbers or dates from a string of characters in a cell? I have a string of characters. My dog has 3330 fleas. I want to place the number in its own cell. -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=277285 |
#4
|
|||
|
|||
Hi Aladin
like your non-array formula approach but this version will only work if the string contains at least one zero. Otherwise your formula returns for me #VALUE. e.g. for 'my dog has 3111 flees' Also embedded numbers such as 3011 would lead to a wrong result (would yeald 301 instead of 3011) "Aladin Akyurek" wrote: =--REPLACE(LEFT(A2,MAX(FIND(0,SUBSTITUTE(A2,{1,2,3,4, 5,6,7,8,9},0)&0)+1)),1,MIN(FIND(0,SUBSTITUTE(A2,{1 ,2,3,4,5,6,7,8,9},0)&0)-1),"") where A2 houses a target string with a number. blackbeemer Wrote: How do you extract numbers or dates from a string of characters in a cell? I have a string of characters. My dog has 3330 fleas. I want to place the number in its own cell. -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=277285 |
#5
|
|||
|
|||
Indeed. The OP should ignore this approach which is a wrong generalization of a solution for different type of task. Frank Kabel Wrote: Hi Aladin like your non-array formula approach but this version will only work if the string contains at least one zero. Otherwise your formula returns for me #VALUE. e.g. for 'my dog has 3111 flees' Also embedded numbers such as 3011 would lead to a wrong result (would yeald 301 instead of 3011) "Aladin Akyurek" wrote: =--REPLACE(LEFT(A2,MAX(FIND(0,SUBSTITUTE(A2,{1,2,3,4, 5,6,7,8,9},0)&0)+1)),1,MIN(FIND(0,SUBSTITUTE(A2,{1 ,2,3,4,5,6,7,8,9},0)&0)-1),"") where A2 houses a target string with a number. blackbeemer Wrote: How do you extract numbers or dates from a string of characters in a cell? I have a string of characters. My dog has 3330 fleas. I want to place the number in its own cell. -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=277285 -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=277285 |
#6
|
|||
|
|||
Copy the entire column and run this macro on that copy.
OR run it on the original column if you don't care to preserve the alphas. 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 Gord Dibben Excel MVP On Wed, 10 Nov 2004 21:17:01 -0800, "blackbeemer" wrote: How do you extract numbers or dates from a string of characters in a cell? I have a string of characters. My dog has 3330 fleas. I want to place the number in its own cell. |
#7
|
|||
|
|||
"Gord Dibben" <gorddibbATshawDOTca wrote...
Copy the entire column and run this macro on that copy. OR run it on the original column if you don't care to preserve the alphas. 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 .... Inefficient. Also questionable including commas. First pass, simplify the If block, access the range's .Value property only once, and call Mid only once per iteration. For Each r In rng v = r.Value t = "" For i = 1 To Len(v) c = Mid(v, i, 1) If c Like "[0-9.]" Then t = t & c Next i r.Value = t Next r Second pass, eliminate the 1-char temp variable. For Each r In rng v = r.Value For i = 1 To Len(v) If Mid(v, i, 1) Like "[!0-9.]" Then Mid(v, i, 1) = " " Next i r.Value = Application.WorksheetFunction.Substitute(v, " ", "") Next r But for maximum flexibility on systems with Windows Script Host installed (so 99.44% of PCs running Windows 98SE or 2K or later, and most running Windows NT4), nothing beats regular expressions. Sub foo() Dim rng As Range, c As Range, re As Object Set rng = Selection.SpecialCells( _ Type:=xlCellTypeConstants, _ Value:=xlTextValues _ ) Set re = CreateObject("vbscript.regexp") re.Pattern = "[^0-9.]+" 'or use an InputBox to set re.Global = True For Each c In rng c.Formula = re.Replace(c.Formula, "") Next c End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract hyperlink string from excel cell | Links and Linking in Excel | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
Extract date from cell | Excel Worksheet Functions | |||
how to count the nr of occurrences of a text string in a cell rang | Excel Worksheet Functions | |||
Add a string to a cell | Excel Worksheet Functions |