Text to Columns

Exercise Solution

1. We declare the variables and find the last line to loop

   Dim LastRow As Long
   Dim i As Integer
   
   LastRow = Cells(Rows.Count, 1).End(xlUp).Row
   

2. As we will use the TextToColumns inside a For Next loop, and will end up subscribing some data, we deactivate Excel alerts to prevent each iteration from receiving an alert (remember to activate it again at the end of the loop).

   Application.DisplayAlerts = False
   

3. We create a For Next loop until the last entry line and use the TextToColumns method. As the problem data is separated by the dash (-) we set this criterion to separate the data in the columns.

   For i = 4 To LastRow
       Cells(i, 1).TextToColumns Destination:=Cells(i, 3), DataType:=xlDelimited, Other:=True, OtherChar:="-"
   

4. We use the Abs function to make Excel consider regional codes written between parentheses as absolute data. This is done because by default Excel consider this kind of data (data between parentheses) as a negative value.

   Cells(i, 4).Value = Abs(Cells(i, 4).Value)
   

5. Subsequently, we aggregate in one single cell every phone number that is separeted by dashes and we bring the name related to the call to the newly created blank cell (the one that previously contained the second part of the phone number)

   Cells(i, 5).Value = Cells(i, 5).Value & "-" & Cells(i, 6).Value
   Cells(i, 6).Value = Cells(i, 7).Value
   

6. We calculate the value of the call by multiplying the number of pulses by the pulse value (this value is given in the exercise statement). Finally, we make the initial letters of proper names capital letters with StrConv and the vbProperCase argument.

       Cells(i, 7).Value = Cells(i, 3) * 1.3
       Cells(i, 6).Value = StrConv(Cells(i, 6), vbProperCase)
   Next i
   

Consolidated Answer

Sub Solution()

    Dim LastRow As Long
    Dim i As Integer

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Application.DisplayAlerts = False

    For i = 4 To LastRow

        Cells(i, 1).TextToColumns Destination:=Cells(i, 3), DataType:=xlDelimited, Other:=True, OtherChar:="-"
        Cells(i, 4).Value = Abs(Cells(i, 4).Value)
        Cells(i, 5).Value = Cells(i, 5).Value & "-" & Cells(i, 6).Value
        Cells(i, 6).Value = Cells(i, 7).Value
        Cells(i, 7).Value = Cells(i, 3) * 0.05
        Cells(i, 6).Value = StrConv(Cells(i, 6), vbProperCase)

    Next i

    Application.DisplayAlerts = True

End Sub