formatting data thread707-633210 question

[JustATheory]

I've tried Skip's code and am receiving an error (End With with out With), not sure what I'm doing wrong. But also, I only need to remove non-numeric charcters, I don't need to reformat the data. Please see thread thread707-633210. Any help is always appreciated.

Skip you are always extremly helpful.


Thanks,
Andy

 

[SkipVought]

Which code is not working. Please post here.

Skip,

_[red][/red]

 

[JustATheory]

'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


Hi Skip,
I've got a test file, with one column of code titled PhoneRange, with data beginning in A2. This data can have some weird stuff in it, besides numbers, I'd just like to be able to remove the non numeric characters.

Thanks,
Andy

 

[PHV]

In fact lack of Next:
End Select
[!]Next[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886