Reading from Excel file

[primagic]

I can read the data from an Excel file fine however I need to be able to format some columns in the spreadsheet.

In one column I have a list of phone numbers some.

I need to remove the spaces from the phone numbers and for numbers less than 11 characters add a leading 0.

the code I am using to loop through the rows

Private Sub processWorksheet(ByVal filename As String, ByVal sheet As Worksheet)

        Dim writeline As String, rng As Range = sheet.UsedRange, rowCount As Int32 = rng.Rows.Count


        For i = 2 To rowCount

            'Create the appropriate client code for the last column depending on what information is contained in column 15 
            'as per original request


            Dim strClientCode As String
            If CType(rng.Cells(i, 15), Range).Value = "Motor" Then
                strClientCode = "TESCOBANKMOT"
            Else
                strClientCode = "TESCOBANKHOM"
            End If


            'Format the phone numbers in column 29 to append the leading 0, remove the spaces and delete those numbers where the letter N is entered.

            Dim strPhoneNumber As String

            If CType(rng.Cells(i, 29), Range).Value = "N" Then
                strPhoneNumber = ""

            Else

                strPhoneNumber = CType(rng.Cells(i, 29), Range).Value

            End If





            'Format the phone numbers in column 30 to append the leading 0, remove the spaces and delete those numbers where the letter N is entered.

            Dim strPhoneNumberOther As String

            If CType(rng.Cells(i, 30), Range).Value = "N" Then
                strPhoneNumberOther = ""


            Else
                strPhoneNumberOther = CType(rng.Cells(i, 30), Range).Value
            End If

Thanks

 

[jbenson001]

strPhoneNumber.Replace(" ", "") ''To remove spaces in the phone number
strPhoneNumber.ToString("D11") '' This will pad the number with leading 0s. The total length of the sting will be 11