Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
blackbeemer
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


=--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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract hyperlink string from excel cell Ryan Sapien Links and Linking in Excel 1 January 20th 05 01:24 AM
Paste rows of numbers from Word into single Excel cell BecG Excel Discussion (Misc queries) 1 December 8th 04 05:55 PM
Extract date from cell Eric Excel Worksheet Functions 3 November 4th 04 07:37 PM
how to count the nr of occurrences of a text string in a cell rang eagerbuyer Excel Worksheet Functions 1 November 4th 04 01:27 PM
Add a string to a cell ramsdesk Excel Worksheet Functions 2 October 28th 04 07:20 AM


All times are GMT +1. The time now is 02:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"