formatting data 1

[mscallisto]

in a row of phone numbers in different formats like (303) 123-4567 or 3031234567 what's a code suggestion that will
remove all non numeric characters and spaces and replace with the format 123-445-5679?

I asked the same question in the VB forum but would like to see the same in VBA.

 

[Bowers74]

Hi mscallisto,

You can use formulae for this, you don't need code:

Assuming your telephone numbers are in column A, enter the following formula into B1 and copy down:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","&quot,"-","&quot,":","&quot,"*","&quot,"_","&quot,"(","&quot,&quot","&quot

This will remove almost any non-numeric character that anyone could possible think to enter in a telephone number

Then enter the following formula into cell C1 and copy down:

=LEFT(B1,3)&"-"&MID(B1,4,3)&"-"&RIGHT(B1,4)

This "inserts" the hyphens "-" where you would like them to be.

I tried to get this to work together in one cell, but the formula was too long. But I think you will be happy with the results, even though you have to use two columns.

I hope this helps!




Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Here's what I would do...

For Each c In PhoneRange
  With c
    sOut = ""
    bPrev = False
    For i = 1 To Len(.Value)
      sByte = Mid(.Value, i, 1)
      Select Case sByte
      Case "0" To "9"
        sOut = sOut & sByte
        bPrev = True
      Case Else
        If bPrev Then _
          sOut = sOut & "-"
        bPrev = False
      End Select
    Next
    .Value = sOut
  End With
Next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mscallisto]

Thanks Bowers74
The formula does work but I needed the vba code.


Thanks Skip

I have no idea what .Value does but I got enough out of your code to do the following:

Dim r As Long
'r is the last row used in the spreadsheet
r = ActiveSheet.UsedRange.Rows.Count

For i = 1 To r

'For phone number, remove all non numeric characters and format as 123-445-6789
sout = ""
bPrev = False
For j = 1 To Len(Rows(i).Columns(4))
sByte = Mid(Rows(i).Columns(4), j, 1)
Select Case sByte
Case "0" To "9"
sout = sout & sByte
bPrev = True
Case Else
If bPrev Then _
sout = sout & "-"
bPrev = False
End Select
Next j
Rows(i).Columns(5) = sout
Next i
End Sub

 

[SkipVought]

I like to used Named ranges and Excel Objects as it makes for nice tight efficient code.

Here are some nots on my code if you're interested...

'set a range assuming that data starts in A2 and is vertically contiguous
Dim PhoneRange as Range
Set PhoneRange = Range(Cells(2,1), Cells(2,1).End(xlDown))
'For Each...Next loops thru each object in a collection
For Each c In PhoneRange
   'references the object and holds that object's node for associated properties and methods
   With c
    sOut = ""
    bPrev = False
    For i = 1 To Len(.Value)
  '.value is identical to c.value but it is more efficient -- it is the value of a single range object in the PhoneRange
      sByte = Mid(.Value, i, 1)
      Select Case sByte
      Case "0" To "9"
        sOut = sOut & sByte
        bPrev = True
      Case Else
        If bPrev Then _
          sOut = sOut & "-"
        bPrev = False
      End Select
  'assign the reconstructed string to c.Value
    .Value = sOut
   End With
Next

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mscallisto]

Wow I didn't know that, I'm impressed!

Thanks very much for sharing this Skip.

sam