Extracting CAPS and NUMBER Strings from single cell

[Deepn3lue]

Hi there, i`m new around.

After browsing this forum for a while via google, to answer most of my Excel challenges, finally i`ve found an opportunity to actually challenge the community here ( or at-least i`d like to believe so) with some an interesting pickle i have on excel. i`d like to point out i`m using the 2013 version.

The issue i`m having is this:

I have the following string on a cell:

[TABLE="width: 692"]
<tbody>[TR]
  [TD="width: 692, align: left"]{ "  B002YFEERO :{ size_name : 3 , ASIN color_name 1 }, B005KLGHMO 4 0 B002XQ7YNK  2 B0030EKLMA B001J4MYKA }},
    
    { "" B003F23N9U B0014ENY2C B005KUKMRG B005KLXGH8 }},[/TD]
[/TR]
</tbody>[/TABLE]

non of it is code actually, all should be considered as plain text.

What i need is to extract to the following cell/column,
Only the STRINGS the start with the capital B
For anyone who`s familer with these kind of strings you
guessed it right, this are amazon products ASINS.

Anyway at first i have applied the following method,
Found on another page of this forum

Function ExtractCap(Rng As Range)Application.Volatile
ExtractCap = ""
For f = 1 To Len(Rng)
If Asc(Mid(Rng.Value, f, 1)) >= 65 And Asc(Mid(Rng.Value, f, 1)) <= 90 Then
ExtractCap = ExtractCap & Mid(Rng.Value, f, 1)
End If
Next f
End Function

The end result here was GOOD, and it did pretty much
what i wanted, only later i noticed it didn`t extract any of the numbers, so what i need is to be able to extract the numbers connected with the CAPS text from this strings

Furthermore, i need to eliminate the numbers,
Extracted after this text within strings

size_name : 3
color_name 1

Leaving me just with NUMBERS followed by CAPS.
i.e B0014ENY2C and not color_name 1

i hope thats even possible,

HUGE Thanks in advance to anyone who puts an effort here.

 

[Deepn3lue]

Another way i thought of going about these is to extract any complete words (no sapce) strings from the cell containing the capped letter B, since all the text strings i`d like to extract start with the letter B.

So its basically extracting any complete words starting with B charcter, untill the SPACE character appears.

hope that helps

 

[István Hirsch]

Do you want all in one cell or different cells?

 

[Deepn3lue]

Do you want all in one cell or different cells?

I can deal with both
if its all in one cell , better have space oranother caharcter i can "replace" later with line break between each of the
wanted strings i mentioned.

 

[István Hirsch]

Enter the formula below in B1 and copy across as needed:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	{ " B002YFEERO :{ size_name : 3 , ASIN color_name 1 }, B005KLGHMO 4 0 B002XQ7YNK 2 B0030EKLMA B001J4MYKA }}, { "" B003F23N9U B0014ENY2C B005KUKMRG B005KLXGH8 }},	B002YFEERO	B005KLGHMO	B002XQ7YNK	B0030EKLMA	B001J4MYKA	B003F23N9U	B0014ENY2C	B005KUKMRG	B005KLXGH8	*
Sheet

 

[Ombir]

VBA alternative:

Sub ExtractNumbers()
Dim txt     As String
Dim match   As Variant
Dim matches As Variant
Dim regex   As Object

Set regex = CreateObject("Vbscript.regexp")

txt = Range("A1")

With regex
    .Global = True
    .MultiLine = True
    .Pattern = "B\w+\s"
End With

Set matches = regex.Execute(txt)

For Each match In matches
  x = x & "," & match.Value
Next match

MsgBox Mid(x, 2)

End Sub