Hey everyone!

I have an XL spreadsheet with tens of thousands of phone numbers, all
entered in different ways. IE

COLUMN A - PHONE NUMBER
973-555-1234
(973) 454-1291
none
(456) 555-3241
283 228 0293
home-(973) 555-1223

See - people entered data all different ways. I can't control the data
entry. But what I need to do is find a way to reformat the results so all
the numbers look like this: (###) ###-####

Can anyone help? Thanks so much!

The following VBA (credit to the original poster) creates a User defined
Function

Alt F11 insert Module and paste the folowing code

Public Function GetNumbers(sText As String)

With CreateObject("vbscript.regexp")
.Pattern = "\D"
.Global = True
GetNumbers = .Replace(sText, "")
End With
End Function

The usage is as follows - assume that the data is in column A in B put the
following formula

=getnumbers(A1)*1 ( The *1 changes it from text to nembers so you can now
apply the custom format that you want)

edvwvw


Access Joe wrote:
Hey everyone!

I have an XL spreadsheet with tens of thousands of phone numbers, all
entered in different ways. IE

COLUMN A - PHONE NUMBER
973-555-1234
(973) 454-1291
none
(456) 555-3241
283 228 0293
home-(973) 555-1223

See - people entered data all different ways. I can't control the data
entry. But what I need to do is find a way to reformat the results so all
the numbers look like this: (###) ###-####

Can anyone help? Thanks so much!

--
Message posted via http://www.officekb.com

Thanks a lot. This worked!

"edvwvw via OfficeKB.com" wrote:

The following VBA (credit to the original poster) creates a User defined
Function

Alt F11 insert Module and paste the folowing code

Public Function GetNumbers(sText As String)

With CreateObject("vbscript.regexp")
.Pattern = "\D"
.Global = True
GetNumbers = .Replace(sText, "")
End With
End Function

The usage is as follows - assume that the data is in column A in B put the
following formula

=getnumbers(A1)*1 ( The *1 changes it from text to nembers so you can now
apply the custom format that you want)

edvwvw


Access Joe wrote:
Hey everyone!

I have an XL spreadsheet with tens of thousands of phone numbers, all
entered in different ways. IE

COLUMN A - PHONE NUMBER
973-555-1234
(973) 454-1291
none
(456) 555-3241
283 228 0293
home-(973) 555-1223

See - people entered data all different ways. I can't control the data
entry. But what I need to do is find a way to reformat the results so all
the numbers look like this: (###) ###-####

Can anyone help? Thanks so much!

--
Message posted via http://www.officekb.com