VBA to extract values starting with specific character

sncb

Board Regular
Joined
Mar 17, 2011
Messages
122
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
1622830344926.png


Output:
1622830232979.png
 

Attachments

  • 1622829996555.png
    1622829996555.png
    15.1 KB · Views: 5

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,036
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Mar 17, 2011
Messages
122
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

1622839143286.png


1622840388036.png


1622840350235.png


Thanks again for your input.
 

Attachments

  • 1622839060493.png
    1622839060493.png
    5.6 KB · Views: 2

sncb

Board Regular
Joined
Mar 17, 2011
Messages
122
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.....

B707B707
B717B747
B727B787a
B737cB717
B747B737
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

Board Regular
Joined
Mar 17, 2011
Messages
122
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Pasting using xl2BB:

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


Expected Output:
Test Bcode.xlsx
AB
2B707B707
3B717B747
4B727B787a
5B737cB717
6B747B737
7B757
Sheet2


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


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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,036
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
What output did you want from this... Airbus 1A; B707: 7

B707

or...

B707; 7
 

sncb

Board Regular
Joined
Mar 17, 2011
Messages
122
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 5, 2021
Messages
927
Office Version
  1. 2010
Platform
  1. Windows
Hi, according to your post #5 attachments a VBA demonstration as a beginner starter :​
VBA Code:
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

Board Regular
Joined
Mar 17, 2011
Messages
122
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, according to your post #5 attachments a VBA demonstration as a beginner starter :​
VBA Code:
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

Well-known Member
Joined
Apr 5, 2021
Messages
927
Office Version
  1. 2010
Platform
  1. Windows
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 …​
 

Watch MrExcel Video

Forum statistics

Threads
1,132,895
Messages
5,655,844
Members
418,246
Latest member
VerticalChris

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top