I don't know where your records start or end but there are when I copied your examples into Excel rows that had multiple telephone numbers. This formula will extract the first telephone number in each row excluding extension numbers but not any of the other telephone numbers. A further clarification of the data format would be appreciated to facilitate giving a better solution. For example: are there really multiple telephone numbers in a row like appears in your example row 2?
The first part of this formula is nearly identical to that of Special-K and works the same except I used Search instead of Find. SEARCH is not case sensitive while FIND is case sensitive. This isn't a consideration when finding numbers as there isn't an upper or lower case number.
Formula:
=IF(ISNUMBER(SEARCH("phone",A1,1)),MID(A1,SEARCH("Phone",A1,1)+7,12),LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),12))
Bookmarks