Hi, there!

The situation goes like that : I have a messy cell that contains both
adresses and a couple of phone numbers per client. The problem is that
i need only the phone numbers. Is there a way that i can exctract each
one of them in a different cell - i mean if this client has 3 numbers
specified in the messy cell, they should be distributed in 3 separated
cells. Also as you can guess the adresses includes numbers, that are
not needed, and basically this can be worked around ( i guess ) by
specifying that we need numbers with more that 5 digits.

P.S. I`m not very advanced users, so I`ll apreciate detailed ( as for
dummies ) explanation.

Thanks in Advance,

Georgi

Hi

You can use "TextToColumns", if address and each phone number has a
seperator like a comma.

Select the cells, goto Data menu TextToColumns, select Delemited Next
select your seperator and follow instructions.

Hopes it helps.

Regards,
Per


"Georgi" skrev i meddelelsen
...
Hi, there!

The situation goes like that : I have a messy cell that contains both
adresses and a couple of phone numbers per client. The problem is that
i need only the phone numbers. Is there a way that i can exctract each
one of them in a different cell - i mean if this client has 3 numbers
specified in the messy cell, they should be distributed in 3 separated
cells. Also as you can guess the adresses includes numbers, that are
not needed, and basically this can be worked around ( i guess ) by
specifying that we need numbers with more that 5 digits.

P.S. I`m not very advanced users, so I`ll apreciate detailed ( as for
dummies ) explanation.

Thanks in Advance,

Georgi

What do the phone numbers look like??

123-456-7890
--
Gary''s Student - gsnu2007k


"Georgi" wrote:

Hi, there!

The situation goes like that : I have a messy cell that contains both
adresses and a couple of phone numbers per client. The problem is that
i need only the phone numbers. Is there a way that i can exctract each
one of them in a different cell - i mean if this client has 3 numbers
specified in the messy cell, they should be distributed in 3 separated
cells. Also as you can guess the adresses includes numbers, that are
not needed, and basically this can be worked around ( i guess ) by
specifying that we need numbers with more that 5 digits.

P.S. I`m not very advanced users, so I`ll apreciate detailed ( as for
dummies ) explanation.

Thanks in Advance,

Georgi

On Tue, 23 Dec 2008 06:16:08 -0800 (PST), Georgi wrote:

Hi, there!

The situation goes like that : I have a messy cell that contains both
adresses and a couple of phone numbers per client. The problem is that
i need only the phone numbers. Is there a way that i can exctract each
one of them in a different cell - i mean if this client has 3 numbers
specified in the messy cell, they should be distributed in 3 separated
cells. Also as you can guess the adresses includes numbers, that are
not needed, and basically this can be worked around ( i guess ) by
specifying that we need numbers with more that 5 digits.

P.S. I`m not very advanced users, so I`ll apreciate detailed ( as for
dummies ) explanation.

Thanks in Advance,

Georgi

Yes, it can be done.

Please post some examples of the cell contents that contains the variations in
telephone number formats for your region.
--ron

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

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

On Sun, 28 Dec 2008 15:47:28 -0500, Ron Rosenfeld
wrote:

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


By the way, should you have, within your data, strings of digits that are
greater than 5, but are not phone numbers (e.g. ... 123456abc) where you do
not want to return the 123456, then we will need to modify the regular
expression pattern: e.g.

"\b\d{5,}\b"

So, the formula would be:

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

Also, with the information you have supplied, there is no way to differentiate
a 5 digit telephone number from a 5 digit address.
--ron

On Sun, 28 Dec 2008 15:47:28 -0500, Ron Rosenfeld
wrote:

I noticed an inappropriate line wrap in the UDF. Below should correct it:

================================
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
=====================================
--ron

On Dec 29, 3:51*am, Ron Rosenfeld wrote:
On Sun, 28 Dec 2008 15:47:28 -0500, Ron Rosenfeld
wrote:

I noticed an inappropriate line wrap in the UDF. *Below should correct it:

================================
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
=====================================
--ron

Hi Ron,

Thanks for your help, but I do have a problem with this.

First of all I forgot to tell you that I`m using Excel 2007.

Here is what I did :
I`ve opened Visual Basic ( by pressing Alt+F11 ), then I`ve copied the
lines you provided and saved the Module. Now I have Module1 in Modules
in the VBA project of the excel document that I wanna exctract phone
numbers from ( I do think that I did that part OK ).
After that I`ve copied the formula that you`ve provided in a cell V2
( the cell that I wanna exctract information from is U2 ), so cell V2
looks like that : " =RegexMid($U2,"\d{5,}",COLUMNS($U:U)) ". This
formula don`t works, so I`ve changed the commas with semicolons, and
V2 looks like that " =RegexMid($U2;"\d{5,}";COLUMNS($U;U)) ". The
problem is that now the formula works, but it returns only the first
phone number of the cell, but not the other ones.

I`m sorry for disturbing you again, but I`m trying to get around this,
but I cannot find what`s wrong (since I am a dummy as I said ).

P.S. I do have a exemplary worksheet with just 5 examples, but as far
as I know in my country there is no case that a adress contains number
with more than 4 digits. Even if we find some, it`s not going to be
that crucial since it`s going to be one in a thousand so we can
correct it manually.

Thank you very much, again,

Georgi

On Mon, 29 Dec 2008 00:10:40 -0800 (PST), Georgi wrote:

On Dec 29, 3:51*am, Ron Rosenfeld wrote:
On Sun, 28 Dec 2008 15:47:28 -0500, Ron Rosenfeld
wrote:

I noticed an inappropriate line wrap in the UDF. *Below should correct it:

================================
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
=====================================
--ron

Hi Ron,

Thanks for your help, but I do have a problem with this.

First of all I forgot to tell you that I`m using Excel 2007.

Here is what I did :
I`ve opened Visual Basic ( by pressing Alt+F11 ), then I`ve copied the
lines you provided and saved the Module. Now I have Module1 in Modules
in the VBA project of the excel document that I wanna exctract phone
numbers from ( I do think that I did that part OK ).
After that I`ve copied the formula that you`ve provided in a cell V2
( the cell that I wanna exctract information from is U2 ), so cell V2
looks like that : " =RegexMid($U2,"\d{5,}",COLUMNS($U:U)) ". This
formula don`t works, so I`ve changed the commas with semicolons, and
V2 looks like that " =RegexMid($U2;"\d{5,}";COLUMNS($U;U)) ". The
problem is that now the formula works, but it returns only the first
phone number of the cell, but not the other ones.

I`m sorry for disturbing you again, but I`m trying to get around this,
but I cannot find what`s wrong (since I am a dummy as I said ).

P.S. I do have a exemplary worksheet with just 5 examples, but as far
as I know in my country there is no case that a adress contains number
with more than 4 digits. Even if we find some, it`s not going to be
that crucial since it`s going to be one in a thousand so we can
correct it manually.

Thank you very much, again,

Georgi


Georgi,

I have Excel 2007 also. But this routine should work on most versions.

You are correct in that the commas should be replaced by whatever your country
separators are -- semicolon is common.

You initially wrote that you wanted the numbers "distributed in 3 separated
cells."

So each cell with the formula will only return one number.

In my instructions, I had written to "Then fill right far enough to be sure to
get all of the returns". Did you do that? What does the formula look like
when you filled it into W2, X2, Y2 etc?

--ron