Thursday, February 19, 2015

Separate Strings through VBA Code

In this tutorial we are going to explain you how you can separate the strings using the VBA Macro Code in Microsoft Excel.

Below Is the Situation in which you need to separate the Full Name string to the First Name and the Last Name string:

Separate Strings Excel Query Image

You can place a command button on your worksheet (as shown in the above screenshot) And add the following code lines: 

  1. We Declare a variable called Full_Name of Type String, a variable called Comma_Position of Type Integer, And a variable called i of Type Integer.
  2.     Dim Full_Name As String, Comma_Position As Integer, i As Integer
    

    The problem we are dealing With Is that we need To tell Excel VBA from where we want To separate the string. In Case of Smith, Mike the comma is at position 6 While in Case of Williams, Janet the comma is at position 9




  3. We use a loop to execute the operations on each name entered in Excel. First, we initialize the variable Full_Name. Next, we use the Instr function to find the position of the comma.
  4.     For i = 2 To 7
        Full_Name = Cells(i, 1).Value
        Comma_Position = InStr(Full_Name, ",")
    
    Note: Currently this code is working from Row No. 2 to 7. You can modify it as per your requirement.

  5. Now, we want to write the part after the comma to Column B and the part in front of the comma to column C. You can achieve this by adding below mentioned lines in you code:
  6. Cells(i, 2).Value = Mid(Full_Name, Comma_Position + 2)
    Cells(i, 3).Value = Left(Full_Name, Comma_Position - 1)
    
    Mid(Full_Name, Comma_Position + 2) means we want the part of Full Name starting at character 'Comma_Position + 2' (this is exactly the first name).

    Left(Full_Name, Comma_Position - 1) means we want the part of Full Name starting at the beginning until character 'Comma_Position - 1' (this is exactly the last name).

  7. Now, close the loop.
  8.     Next i
    


  9. Final Code will look like below:
  10. Sub Separate_Strings()
    
        Dim Full_Name As String, Comma_Position As Integer, i As Integer
    
        For i = 2 To 7 'You can modify it as per your requirement.
            Full_Name = Cells(i, 1).Value
            Comma_Position = InStr(Full_Name, ",")
        
        Cells(i, 2).Value = Mid(Full_Name, Comma_Position + 2)
        Cells(i, 3).Value = Left(Full_Name, Comma_Position - 1)
        
        Next i
    
    End Sub
    
  11. To test the program, Add six names separated by a comma and space to Range("A2:A7") as shown in the above screenshot.
  12. Test the program.

Result:

Separate Strings Excel Result Image 



No comments:

Post a Comment