VBA - Extract Substring from String in Excel

Few days back, while working on a project, I came across a scenario where I have to extract name of city from the string from millions of line items. It made me to write a piece of VBA code to extract. Although to perform the task manually or with the help of formula was tedious job and my excel sheet use to get hang. I am sharing the snippet with example for you all, you can use this for your reference too.


Using InStr function, I could extract the specific word.








InStr function looks for the position of specified substring within the string and returns the first position of its occurrence. In below example I want to extract the name of cities and the position of names in each string is changed some of them are at start of the string, some in middle, some at last of sentence.


Using If-End If statement if position of substring is greater then 0 it means matching substring is available in string. Now if found enter the value in specified range.

Here is the code.

Option Explicit


Sub ExtractStringfromCell()
    Dim lg_lastrow      As Long
    Dim int_Counter     As Integer
    Dim wks_Worksheet   As Worksheet
   
   
Set wks_Worksheet = ThisWorkbook.Sheets("Sheet1")
   
    wks_Worksheet.Activate
   
        lg_lastrow = wks_Worksheet.Cells(Rows.Count, 2).End(xlUp).Row
       
       
        For int_Counter = 2 To lg_lastrow
            If InStr(Range("B" & int_Counter).Value, "Banglore") > 0 Then
                Range("C" & int_Counter).Value = "Banglore"
            ElseIf InStr(Range("B" & int_Counter).Value, "Mumbai") > 0 Then
                Range("C" & int_Counter).Value = "Mumbai"
            ElseIf InStr(Range("B" & int_Counter).Value, "Bhilai") > 0 Then
                Range("C" & int_Counter).Value = "Bhilai"
            ElseIf InStr(Range("B" & int_Counter).Value, "Delhi") > 0 Then
                Range("C" & int_Counter).Value = "Delhi"
            ElseIf InStr(Range("B" & int_Counter).Value, "London") > 0 Then
                Range("C" & int_Counter).Value = "London"
            ElseIf InStr(Range("B" & int_Counter).Value, "Jaipur") > 0 Then
                Range("C" & int_Counter).Value = "Jaipur"
            ElseIf InStr(Range("B" & int_Counter).Value, "Ooty") > 0 Then
                Range("C" & int_Counter).Value = "Ooty"
            Else
                Range("C" & int_Counter).Value = "Mysore"
            End If
        Next int_Counter
       
End Sub








Hope you understood the logic.