On Sun, 28 Dec 2008 10:43:56 -0800 (PST), Georgi wrote:

First of all I would like to say Merry Christmas to all of you that
are celebrating it and thanks for the input !

@ Per : the only separators are spaces so TextToColumns, will give me
10 cells with different information, so again I need a way to select
only the ones that I need ( the cells with phone number )

@ Gary, @ Ron : here is the content of a exemplary cell :

adr:Sofia, P.K. 1715 Druzhba district 4 flat 407 entrance 6 floor 6
apartment 38 9754543 0898892158 9754543 000

The numbers that we need are 9754543 ; 0898892158 ; 9754543,
everything else is needless, i.e. the phone numbers contains between 5
and 10 ( maybe in some case more ) digits without spaces or dashes in
it. Also the number of phone numbers provided can vary, i.e. the cell
can contain 1,2,3,4,5 different numbers that we should extract.

I`m really confused and I`ll be really grateful if you can help me.
Thank you in advance !

Georgi

Using a UDF (User Defined Function) making use of Regular Expressions (see
below for details):

With your data in, for example, A1, to extract all instances of 5 or more
consecutive digits:

B1: =RegexMid($A1,"\d{5,}",COLUMNS($A:A))

Then fill right far enough to be sure to get all of the returns

To be able to use this UDF:

<alt-F11 opens the VBEditor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens:

===========================================
Option Explicit

Function RegexMid(Str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional MultiLin As Boolean = False) _
As Variant 'Variant as value may be string or array

'Index -- negative values return groups counting from end of string

Dim objRegExp As Object
Dim objMatch As Object
Dim colMatches As Object

Dim i As Long 'counter
Dim T() As String 'container for array results

' Create a regular expression object.
Set objRegExp = CreateObject("vbscript.regexp")

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Set multiline
objRegExp.MultiLine = MultiLin

'Test whether the String can be compared.
If (objRegExp.Test(Str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(Str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim T(1 To UBound(Index))
For i = 1 To UBound(Index)
T(i) = colMatches(IIf(Index(i) 0, Index(i) - 1, Index(i) +
colMatches.Count))
Next i
RegexMid = T()
Else
RegexMid = CStr(colMatches(IIf(Index 0, Index - 1, Index +
colMatches.Count)))
If IsEmpty(RegexMid) Then RegexMid = ""
End If
On Error GoTo 0 'reset error handler
Else
RegexMid = ""
End If
End Function
=====================================

Regular Expressions
http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://msdn2.microsoft.com/en-us/library/ms974619.aspx
http://www.regex-guru.info/

--ron