Keeping only specfic text in a cell

Solved/Closed
Report
Posts
3
Registration date
Friday March 26, 2010
Status
Member
Last seen
March 26, 2010
-
Posts
3
Registration date
Friday March 26, 2010
Status
Member
Last seen
March 26, 2010
-
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


3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
3
Registration date
Friday March 26, 2010
Status
Member
Last seen
March 26, 2010

WOW, thanks very mcuh
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What version of xl you have ?
Posts
3
Registration date
Friday March 26, 2010
Status
Member
Last seen
March 26, 2010

Sorry 2003