Keeping only specfic text in a cell

Solved/Closed

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

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
How to delete a cell in word - Guide
Excel multiple lines in one cell - Guide
Based on the values in cells b77 b81 what function can automatically return the value in cell c77 ✓ - Excel Forum
Insert a function in cell b2 to display the current date from your system. ✓ - Excel Forum
Activate cell - Excel Forum

3 responses

Réponse 1 / 3

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

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