Excel - Keeping only specific text in a cell

December 2016




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 :

This document entitled « Excel - Keeping only specific text in a cell » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.