VBA to extract values starting with specific character

[sncb]

Hi all,

I paste two cells of data into A1 and B1 with different formats and spacing like below and I need to extract only those values that start with a capital 'B' and paste them below the first row in separate rows. What needs to be extracted always start with a 'B' and need to be always whole alphanumeric values like B747 or B737c.

Right now Im doing this manually, value by value and its getting very tedious. I tried with text to columns but each time the format is different and separators are different too so always end up doing it manually. Any help with VBA is really appreciated. Thank you.

Input:

Output:

 

[Rick Rothstein]

You say "right now Im doing this manually, value by value and its getting very tedious" which suggests to me that you have more than 2 cells worth of data to process. Since the two cell values you did post are structured completely differently from each other, I have to wonder about the structure of the cells you did not show us. Can you tell us more about ALL the data you have to process and also tell us how that data is arranged on your worksheet (all across row 1 only)?

 

[sncb]

Hi Rick,

Thank you for your response.

Yes you are right I should have been more clear. When I wrote that I process this manually, value by value, this is what I do:

1. Paste two cells of data anywhere in a sheet like shown in the input screenshot.
2. Start to extract the B values. I simply copy and then paste them below row after row (see the output screenshot in my first post - which ive been doing manually) and then do one copy of all rows and paste it into my destination file.
3. Clear the 2 columns and then paste two cells of new data in this sheet again to extract next set of B values and so on.

It really dosent matter where I paste these 2 cells since essentially Im only trying to extract the B values. I could do this in notepad as well but just wrote A1 and B1 to make it simple to understand or if it helped for the code.

Now about the data format that I paste each time, assuming I paste data in A1 and B1, the format in B1 cell tends to be consistent whereas the format in A1 can change. As you can see below, the B values in the B cell always have a semi colon before it but is also never the first value (its always Boeing, Learjet, Green or whatever). There is no such consistency for the values in the A cell.

Some examples of input data before I start extracting the B values:

Thanks again for your input.

 

[sncb]

Hi Rick,

Not sure if the info I gave was clear but here is the raw data in case it might help. Specific cell location for input dosent really matter so A1 and B1 for input cells would be simplest.

Input:
A1 B1

NarrowBody - 4 - B707 NarrowBody - 2 - B717 NarrowBody - 1 - B727 MediumBody - 1 - B737c LargeBody - 1 - B747 LargeBody - 3 - B757

Boeing (Green);B707 | Toulouse 2;B747| Airbus 1;B787a | Learjet 1;B717 | Boeing 1;B737 |

Expected Output:
A2/A3/A4/A5.... B2/B3/B4/B5.....

B707	B707
B717	B747
B727	B787a
B737c	B717
B747	B737
B757

Other examples of Input:
A1 B1

Boeing. 1A, B737 Airbus 1A; B707: 7 Airbus 1B; B747: 5 Learjet 2; B717: 1 Boeing 2; B787: 2

Learjet 1C;B737 | Boeing. 1;B717 | Airbus3;B747

Airbus 3, B787 Boeing 4, B737 Learjet 4, B747 Boom 2, B707 Airbus 2, B717

Green. 3;B717 | Boeing(Brown);B747 | Airbus (Red);B737 | Airbus. 2;B787

 

[sncb]

Pasting using xl2BB:

Input:

Test Bcode.xlsx
	A	B
1	NarrowBody - 4 - B707 NarrowBody - 2 - B717 NarrowBody - 1 - B727 MediumBody - 1 - B737c LargeBody - 1 - B747 LargeBody - 3 - B757	Boeing (Green);B707 | Toulouse 2;B747| Airbus 1;B787a | Learjet 1;B717 | Boeing 1;B737 |
Sheet2

Expected Output:

Test Bcode.xlsx
	A	B
2	B707	B707
3	B717	B747
4	B727	B787a
5	B737c	B717
6	B747	B737
7	B757
Sheet2

Other examples of Input:

Test Bcode.xlsx
	A	B
1	Boeing. 1A, B737 Airbus 1A; B707: 7 Airbus 1B; B747: 5 Learjet 2; B717: 1 Boeing 2; B787: 2	Learjet 1C;B737 | Boeing. 1;B717 | Airbus3;B747
Sheet3

Test Bcode.xlsx
	A	B
1	Airbus 3, B787 Boeing 4, B737 Learjet 4, B747 Boom 2, B707 Airbus 2, B717	Green. 3;B717 | Boeing(Brown);B747 | Airbus (Red);B737 | Airbus. 2;B787
Sheet4

 

[Rick Rothstein]

What output did you want from this... Airbus 1A; B707: 7

B707

or...

B707; 7

 

[sncb]

Hi Rick,

Thanks for your help with this.

B707 is what I'm trying to extract in the example you mentioned.

Thanks again.

 

[Marc L]

Hi, according to your post #5 attachments a VBA demonstration as a beginner starter :​

Sub Demo1()
    Dim V, R&, W
        V = Split([A1].Text, vbLf)
        For R = 0 To UBound(V):  W = Split(Split(V(R), ":")(0)):  V(R) = W(UBound(W)):  Next
        [A2].Resize(UBound(V) + 1).Value2 = Application.Transpose(V)
        V = Split(Split([B1].Text, vbLf)(0), "|")
        For R = 0 To UBound(V) + (V(UBound(V)) = ""):  V(R) = Split(V(R), ";")(1):  Next
        [B2].Resize(UBound(V) + 1).Value2 = Application.Transpose(V)
End Sub

 

[sncb]

Hi, according to your post #5 attachments a VBA demonstration as a beginner starter :​

Sub Demo1()
    Dim V, R&, W
        V = Split([A1].Text, vbLf)
        For R = 0 To UBound(V):  W = Split(Split(V(R), ":")(0)):  V(R) = W(UBound(W)):  Next
        [A2].Resize(UBound(V) + 1).Value2 = Application.Transpose(V)
        V = Split(Split([B1].Text, vbLf)(0), "|")
        For R = 0 To UBound(V) + (V(UBound(V)) = ""):  V(R) = Split(V(R), ";")(1):  Next
        [B2].Resize(UBound(V) + 1).Value2 = Application.Transpose(V)
End Sub

Hi Marc,

This is working as expected for the data formats I displayed. Thanks for your help with this. Wish you a nice day.

Regards

 

[Marc L]

​

Thanks but it's a bit early as I'm expecting Rick's solution if he goes to RegExp - 'safer' - before I try this way …​