Extract all the words (comma seperated) that start with specific combination of alphabets and numbers from a string

[sarad agarwal]

Hi All,

I want to Extract all the words (comma separated) that start with specific combination of alphabets and numbers from a string.

For example: if my string is as follows:

1) "My name is Sarad and my phone number is B023456 and the number of my car is B09876."

Now I want all the words that start with B0 in the string above, so my output should be B023456,B09876.

Any help is much appreciated.[TABLE="width: 426"]
<tbody>[TR]
[TD="class: xl63, width: 426"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

If you have the TEXTJOIN function (new in some versions of Excel 2016 or 365), you can use the formula in A3. If not, you'll have to use some variant of VBA, such as the UDF in A5:

	A
My name is Sarad and my phone number is B023456 and the number of my car is B09876.
B023456,B09876.
B023456,B09876.

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]=getb0(A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]{=TEXTJOIN(",",TRUE,IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),2)="B0",TRIM(LEFT(SUBSTITUTE(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),LEN(A1))," ",REPT(" ",LEN(A1))),LEN(A1))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Public Function GetB0(ByVal target As Range)
Dim w As Variant, x As Variant

    w = Split(target, " ")
    For Each x In w
        If Left(x, 2) = "B0" Then GetB0 = GetB0 & x & ","
    Next x
    If Len(GetB0) > 0 Then GetB0 = Left(GetB0, Len(GetB0) - 1)
End Function

 

[Joe4]

I would create my own function in VBA to do it, like this:

Function MySplit(myCell As Range)

    Dim txtString As String
    Dim counter As Integer
    Dim wordVar() As String
    Dim resString As String

    txtString = myCell.Value
    wordVar = Split(txtString, " ")
    For counter = 0 To UBound(wordVar)
        If Left(wordVar(counter), 2) = "B0" Then
           resString = resString & wordVar(counter) & ","
        End If
    Next counter

    If Len(resString) > 0 Then MySplit = Left(resString, Len(resString) - 1)

End Function

Then, you can use it like any other Excel function.
So, if your sentence was in cell H1, just use this formula:

=mysplit(H1)