Need help on copying Multiple words in a cell based on number of characters or starts with.

[vinoanbu]

Hi,

I need a VBA code for following requirement
I'm Working in an excel report in which the range column "B" have instructions with multiple part numbers.
I need to copy only those part numbers from column "B" to the next columns C,D,E,F etc., (refer below table)
regarding the part number it always starts with 12AB and it has 14 characters only.

A	B	C	D	E	F	G
1	Install DVR (12AB_46A_386CD) to the IO side and fix the CON (12AB_20A_124AC) towards the PWR unit. Hold TERM (12AB_10C_026DC)	12AB_46A_386CD	12AB_20A_124AC	12AB_10C_026DC
2	Fix PWR UNIT (12AB_90Q_096CD) and check I/O PIN (12AB_40D_006AC)	12AB_90Q_096CD	12AB_40D_006AC

Thanks,
Vino

 

[hagia_sofia]

If you are not against a formula:

=IFNA(DROP(REDUCE("",A1:A2,LAMBDA(a,b,VSTACK(a,
LET(
split,TEXTSPLIT(b,{" ","(",")"}),
string,LEFT(split,4)="12AB",
FILTER(split,string=TRUE))))),1),"")

 

[vinoanbu]

If you are not against a formula:

=IFNA(DROP(REDUCE("",A1:A2,LAMBDA(a,b,VSTACK(a,
LET(
split,TEXTSPLIT(b,{" ","(",")"}),
string,LEFT(split,4)="12AB",
FILTER(split,string=TRUE))))),1),"")

Hi, Thanks for the formula but I prefer to have a VBA code.

Thanks,
Vino

 

[vinoanbu]

If you are not against a formula:

=IFNA(DROP(REDUCE("",A1:A2,LAMBDA(a,b,VSTACK(a,
LET(
split,TEXTSPLIT(b,{" ","(",")"}),
string,LEFT(split,4)="12AB",
FILTER(split,string=TRUE))))),1),"")

Hi hagia,

I tried the formula, but it shows error in this part ("",A1:A2, could you check on this?

Thanks,
Vino

 

[hagia_sofia]

Are there any blank rows within your array of interest?

 

[vinoanbu]

Are there any blank rows within your array of interest?

Hi Hagia,

I don't see any blank rows. I captured the error and attached here for your reference.

Thanks,
Vino

 

[Fluff]

Another formula option if the only thing in brackets are the part numbers.

Fluff.xlsm
	A	B	C	D	E	F
1
2	1	Install DVR (12AB_46A_386CD) to the IO side and fix the CON (12AB_20A_124AC) towards the PWR unit. Hold TERM (12AB_10C_026DC)	12AB_46A_386CD	12AB_20A_124AC	12AB_10C_026DC
3	2	Fix PWR UNIT (12AB_90Q_096CD) and check I/O PIN (12AB_40D_006AC)	12AB_90Q_096CD	12AB_40D_006AC
Data

Cell Formulas
Range	Formula
C2:E2,C3:D3	C2	=TOROW(INDEX(TEXTSPLIT(B2,"(",")",1),,2))
Dynamic array formulas.

You may need to change the commas to semi-colons.

 

[hagia_sofia]

Hi Hagia,

I don't see any blank rows. I captured the error and attached here for your reference.

Thanks,
Vino

I cannot figure out what is the problem, it does not return this message on my computer (do you have 365?); the only thing is that you should adjust the array to B1:B2 but that should not be a problem...