CODEWITHSUNDEEP
excelvba
user1664717
user1664717

Reputation: 1

Extract first four characters after a special character ("|") in excel

I have the below contents in a cell

0001 abc|0002 eXtreme Scale|0003 Infrastructure dept. no.|0004 Integration Components

I would like to extract only the numbers (0001,0002 etc.). Can anybody please help?

Thanks in advance.

Upvotes: 0

Views: 435

Answers (3)

zedfoxus
zedfoxus

Reputation: 37099

Create a function that you could use in multiple ways.

Insert a module and type (copy/paste) this in:

Public Function PipeNumberExtractor(CellValue As String) As String
    Dim PipeSplit() As String
    PipeSplit = Split(CellValue, "|")

    Dim PipeSplitItem As Variant
    Dim SpaceSplit() As String

    For Each PipeSplitItem In PipeSplit
        SpaceSplit = Split(PipeSplitItem, " ")
        If UBound(SpaceSplit) >= 0 Then
            PipeNumberExtractor = PipeNumberExtractor & "," & SpaceSplit(0)
        End If
    Next PipeSplitItem

    If Len(PipeNumberExtractor) > 0 Then
        PipeNumberExtractor = Mid(PipeNumberExtractor, 2)
    End If

End Function

' To test the function, let's do this    
Sub Test()
    Debug.Print PipeNumberExtractor("0001 abc|0002 eXtreme Scale|0003 Infrastructure dept. no.|0004 Integration Components")
End Sub

In Excel, if A1 contains 0001 abc|0002 eXtreme Scale|0003 Infrastructure dept. no.|0004 Integration Components, in A2 you can type 

=PipeNumberExtractor(A1)

And you will get

0001,0002,0003,0004

Upvotes: 0

Michaël Benjamin Saerens
Michaël Benjamin Saerens

Reputation: 875

You can either loop over your string

Dim strNumbers = left(cellcontent, 4)
Dim nPipeLoc = InStr(5, cellcontent, "|", Text)
While ( nPipeLoc > 0)
    strNumbers = strNumbers & "," & Mid(cellcontent, nPipeLoc, 4)
    nPipeLoc = InStr(nPipeLoc + 1, cellcontent, "|", Text)
End While

Or you can use a RegEx

Dim strSearchRegex = "(?:^|\|)(\d{4})"

Or the solution of @KazimierzJawor, which is much simpler :)

Upvotes: 2

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

The following code will write all numbers to cells in column A starting from A1 to the bottom:

Sub NextTest()

    Dim i As Integer
    Dim tmpArr As Variant
    Dim strTxt As String
        strTxt = "0001 abc|0002 eXtreme Scale|0003 Infrastructure dept. no.|0004 Integration Components"
        tmpArr = Split(strTxt, "|")

    For i = 1 To UBound(tmpArr)
        Cells(i, "A") = "'" & Left(tmpArr(i - 1), 4)
    Next i
End Sub

Upvotes: 2

Related Questions