extracting phone & fax #

[mikeo1313]

I have a folder of xlsx's & csvs with phone & fax data in an address column that I want to put into their separate phone and fax column.

Ideally, I think the following rules would extract the desired data successfully.

1. Not all numbers pertain to phones (i.e. zipcodes) not all phone numbers are preceeded by anything that identifies it (ie. phone, ph, tel.) not all records have a phone, fax or either.

2. Their are atleast 10+ consecutive numbers in a phone (with international codes & dialing can be 13-15 numbers) which might have a few of any of the following separators [.],[-],[)],[(] or A single space[ ].

3. The first occurance of the strand of numbers as described in #2 is the phone #, the second occurance is the fax #.


I allready managed to run a script on a folder of files. I just noticed that it will be unsustainable for me to go into the records one by one and manually extract the phone and fax #'s since there are many.

[Ken Puls]

I think you're going to have a heck of a time with that one, Mike. The challenge you get here is that you need to be able to define the logic that the macro needs to work with. If you can't find a common item to work with, that's going to be a real challenge.

So we have that there are 10 numbers with mixed separators. Do all of your records have a zip code? And is it always 5 digits long with no separators? Is there something you can look for in a pattern to say that:
-If it has a 5 digit string, start to the right of that or,
-If there is no 5 digit string, start at the beginning as there is no zip code?

What are you going to do if you run into a fax with no phone number?

[mikeo1313]

If theirs a method that allows you to trove a cells contents character for character, for a whole column, IMO the best way to trap the data will be to have a counter that counts consecutive numeric digits.

1. a flag can be thrown when a numeric digit is found
2. the counter begins counting & aggregating numeric digits to the exception of common separators as period, space and dash
3. another flag can be thrown once an alphanumeric character or 2 consecutive spaces.
4. if counter is greater then 10, the first time around, the agggregated string gets placed in the phone column of the record. The second time around, it gets placed in the fax column.
5. when the end of cell contents is reached the counters & strings have to be reset for the next cell.


Ken, in considering your comment about zip code, their usually spaced apart. I've seen many zipcodes with alphanumeric characters, for the international ones. The zipcodes have no use and are usually spaced more then 2 consecutive spaces from any of the phone #'s.

Encountering just a fax can happen, but I still haven't seen one record yet that just has a fax. In most cases we atleast have the email so just having the fax is not going to be much of a problem.

[GTO]

Could you attach an example file? Fake data of course, and how many rows/examples not all that important, as long as patterns are covered.

[Ken Puls]

Re the zip code, I live in Canada, so I'm intimately familiar with ours... A#A #A#. But you're right, other countries are different. I just didn't know if you were working with only US Data.

I'll give you a bit to get you started:
[VBA]Sub test()
Dim lChars As Long
Dim lCurrent As Long
Dim lRows As Long
Dim lRow As Long
Dim rngData As Range
rngData = Selection
lRows = rngData.Rows.Count
For lRow = 1 To lRows
lChars = Len(rngData(lRow, 1).Value)
For lCurrent = 1 To lChars
'Decide what you want to do in here
Next lCurrent
lChars = 0
lCurrent = 0
Next lRow
End Sub[/VBA]