Keeping only specfic text in a cell

Solved/Closed

noobkelsey Posts 3 Registration date Friday March 26, 2010 Status Member Last seen March 26, 2010 - Mar 26, 2010 at 08:55 AM
noobkelsey Posts 3 Registration date Friday March 26, 2010 Status Member Last seen March 26, 2010 - Mar 26, 2010 at 10:39 AM

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.

Thanks in advance for any help

Keeping only specfic text in a cell
Clear only the formatting from the selected cell - Guide
Cell phone codes - Guide
Based on the cell values in cells b77 ✓ - Excel Forum
Excel cell color formula - Guide
If cell contains text then return value multiple conditions ✓ - Excel Forum

3 responses

Answer 1 / 3

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 26, 2010 at 10:28 AM

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