I have a question that I have not seen answered.

tuaca12000 · #1 05-18-2017, 12:47 PM

Can someone please point me to a forum that addresses this or if there is none, can someone please answer this question. I have a report that is sent to us as a .txt file that we then go in and copy to an Excel Spreadsheet. We then separate the columns using the Text to Columns feature. The question I have is that one of those columns contains a list of phone numbers in the following format (###)###-####. I need to see if it is possible to take that column and reformat it so that the parenthesis and the hyphen are no no longer in the string of numbers so that I just have the 10 digit number formatted like this: ##########. Please help. The only way I have been able to do this is to separate the phone numbers using the Text to Columns feature and place separators between the parenthesis and the numbers, and between the hyphen and the numbers, then go in and delete the columns that contain the parenthesis and the hyphen. However, that creates three distinct columns. I need a way for all 10 digits to be in one cell so that I can then copy and paste those numbers into another system.

NBVC · #2 05-18-2017, 01:18 PM

You can do a Find/Replace for each of those 3 characters within that column.

Logit · #3 05-18-2017, 03:10 PM

.
.

Code:

Option Explicit

Sub CleanAll()
    Dim rng As Range

    For Each rng In Sheets("Sheet1").Range("A1:A15").Cells 'adjust sheetname and range accordingly
        rng.Value = AlphaNumericOnly(rng.Value)
    Next
End Sub
Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

Paste the above into a Routine Module. Activate with a CommandButton on the
sheet in question.

riothecat · #4 05-26-2017, 07:13 AM

after you do text to columns, in a cell next to your 3 cells of numbers, put =a1&b1&c1 (or whatever your 3 cells are) then delete the 3 cells of numbers.

GEBOB taught me that one.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro Needed to Insert Asnwer to A Question in Multiple Choice Format Question	rsrasc	Word VBA	7	03-28-2014 12:28 PM
Track un-answered emails ?	antoinel	Outlook	0	02-23-2011 05:36 AM
Need help with this question...	lllDNYlll	Outlook	0	05-04-2006 07:17 AM
Categories question & replying with attachment question	glitzymama	Outlook	0	03-15-2006 09:32 AM
Outlook 2002 deletes or moves answered messages	Ken Winter	Outlook	3	11-11-2005 10:53 AM