CODEWITHSUNDEEP
excelvbaexcel-2010
Andy
Andy

Reputation: 383

Excel 2010: Strip Substrings From Cell For Text To Columns

I have a spreadsheet that has a column that has variable data in it's cells. A sample cell in this column has the following contents.

P5V010000167908-PBD-12300026-01, P5V010000167904-PBD-12300026-01, P5V010000167906-PBD-12300026-01, P5V000000581894-UNDEFINED, P5V000000581895-UNDEFINED, P5V000000581896-

I want to strip out each P5V number (eg. P5V010000167908, P5V010000167904, P5V010000167906, etc..) and place them into their own column to the right of this column.

The P5V number always starts with P5V and is always 15 characters in length. How could I do a text to columns with only the P5V numbers? Any ideas?

Thanks Andy

Upvotes: 0

Views: 84

Answers (2)

DAXaholic
DAXaholic

Reputation: 35408

You could create the following new UDF (User Defined Function) by just inserting it into a VBA module and then use it in the cells to the right of the string with increasing 'n' parameter.
E.g. if the string is in cell C4 the formula for D4 would be =ExtractP5V($C$4; 1), the formula for E4 would be =ExtractP5V($C$4; 2) and so on.

Function ExtractP5V(str, n) As String
    Dim curStart As Integer
    curStart = 1
    Dim i As Integer
    For i = 1 To n Step 1
        Dim nextP5VPos As Integer
        nextP5VPos = InStr(curStart, str, "P5V")
        If nextP5VPos = 0 Then
            ExtractP5V = "-"
            Exit Function
        End If
        If i = n Then
            ExtractP5V = Mid(str, nextP5VPos, 15)
            Exit Function
        End If
        curStart = nextP5VPos + 1
    Next i
End Function

Hope that helps - btw: the code is not super clean ... :)

Upvotes: 0

Doug Glancy
Doug Glancy

Reputation: 27488

You can do this with two Find/Replaces, followed by Text-to-Columns:

  1. Replace -*, with blanks
  2. Replace - with blanks
  3. Split using Text-to-Columns using a Space delimiter

When I do this with the Macro Recorder on, and clean it up a bit, I get:

Sub Macro2()
    With ActiveSheet.Columns("A:A")
        .Replace What:="-*,", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        .Replace What:="-", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
            TrailingMinusNumbers:=True
    End With
End Sub

Upvotes: 1

Related Questions