Excel - Keeping only specific text in a cell

March 2017




Issue


I have an excel file that has data dumped from external source (P6). One cell contains a lot of data of which I only need very specific.

Here is what 5 cells looks like:
SEA-MVRV, SEA-RAD 
SEA-MVAOV, SEA-OPSSRO 
SEA-MVAOV1 
SEA-CRNPOLAR, SEA-NPCOE, SEA-MMJBC, SEA-RAD, SEA-MVMM 
SEA-CRNPOLAR, SEA-MMJBC, SEA-RAD, SEA-NPCOE, SEA-MVMM 


I would like to delete the SEA- and then keep only the abbreviations that start with MV.

Solution


Assumptions
  • 1. The data is in column A
  • 2. The result is to be shown in column B



Sub extractMV()

Dim lMaxRow As Long
Dim rowIdx As Long
Dim inString As String
Dim outString As String
Dim sTemp As String
Dim iLoc As Integer

    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    For rowIdx = 2 To lMaxRows
        inString = Trim(Cells(rowIdx, "A"))
        outString = ""
        iLoc = 0
        sTemp = ""
        
        iLoc = InStr(1, inString, ",")
        
        Do While (iLoc > 0)
        
            sTemp = Trim(Left(inString, iLoc - 1))
            
            If (Left(sTemp, 6) = "SEA-MV") Then
                outString = outString & ", " & Mid(sTemp, 5)
            End If
                     
            inString = Trim(Mid(inString, iLoc + 1))
            iLoc = InStr(1, inString, ",")
            
        Loop
        
        If (Left(inString, 6) = "SEA-MV") Then
            outString = outString & ", " & Mid(inString, 5)
        End If
            
        If (Left(outString, 1) = ",") Then
            outString = Trim(Mid(outString, 2))
        End If
        
        
        Cells(rowIdx, "B") = outString
    Next
    
End Sub

Note


Thanks to rizvisa1 for this tip on the forum.

Related


Published by aakai1056.
This document, titled "Excel - Keeping only specific text in a cell," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).