Remove everything between commas if string matches?

[angio]

Hello,

I'm hoping someone could help or show me a macro that would do this.

In B2 I have the following

{165}Finishes: S[{2297}Polished Brass,{2298}Raw Brass,{2299}Brushed Brass,{2300}Rubbed Brass,{2301}Antique Brass,{2302}Weathered Bronze,{2303}Pewter,{2304}Polished Chrome,{2305}Polished Nickel,{2306}Antique White,{2307}Oil Rubbed Bronze,{2308}Rust,{2309}Matte Black,{2310}Verde]


In C2, D2, E2 I have

Polished Brass Weathered Bronze Pewter

I need a macro that will match C2 D2 E2 in B2 keep everything between the commas and inside the brackets [ ] and remove all other entries. So whats left should look like

{165}Finishes: S[{2297}Polished Brass,{2302}Weathered Bronze,{2303}Pewter]

Some entries will have more options past e2 going on to f2 and more. Maybe those entries in columns C2, D2, E2 should be in one cell commas separated to make a macro easier? Any help would be really appreciated.

Thanks

 

[cjcobra]

Instead of removing all commas, just build the statement you want.

Do they always have the same {number} for each item? Have those in a table, and build a concatenate and vlookup formula.

something like:
=vlookup(c2) & C2 & "," & vlookup(d2)...

 

[angio]

Those numbers are all unique for every entry

 

[cjcobra]

always a four digit code? I've pretty much got it made if so.

 

[angio]

The ID # before Finishes is 3 digit, all other ID's between the [] are 4

First row
{166}Finishes: S[{2311}Polished Brass,{2312}Raw Brass,{2313}Brushed Brass,{2314}Rubbed Brass,{2315}Antique Brass,{2316}Weathered Bronze,{2317}Pewter,{2318}Polished Chrome,{2319}Polished Nickel,{2320}Antique White,{2321}Oil Rubbed Bronze,{2322}Rust,{2323}Matte Black,{2324}Verde]

Last row
{111}Finishes: S[{1541}Polished Brass,{1542}Raw Brass,{1543}Brushed Brass,{1544}Rubbed Brass,{1545}Antique Brass,{1546}Weathered Bronze,{1547}Pewter,{1548}Polished Chrome,{1549}Polished Nickel,{1550}Antique White,{1551}Oil Rubbed Bronze,{1552}Rust,{1553}Matte Black,{1554}Verde]

 

[pgc01]

Hi
Welcome to the board

This is a udf solution.

In A2: =StripCodes(B2,C2:E2)
In A3: =StripCodes(B3,C3:F3)

(first parameter the text, second parameter a contiguous horizontal vector with one code per cell, no leading or trailing spaces)

Function StripCodes(sText As String, rCodes As Range) As String
Dim sCodes As String
 
sCodes = Join(Application.WorksheetFunction.Index(rCodes.Value, 0), "|")
With CreateObject("VBScript.RegExp")
    .Pattern = "\{\d{4}\}(?!" & sCodes & ")[a-zA-Z ]+,?"
    .Global = True
    StripCodes = Replace(.Replace(sText, ""), ",]", "]")
End With
End Function

Ex.:

Book1.xlsm
	A	B	C	D	E	F
1
2	{165}Finishes: S[{2297}Polished Brass,{2 298}Raw Brass,{2303}Pewter,{23 08}Rust]	{165}Finishes: S[{2297}Polished Brass,{2 298}Raw Brass,{2299}Brushed Brass,{2300} Rubbed Brass,{2301}Antique Brass,{2302}W eathered Bronze,{2303}Pewter,{2304}Polis hed Chrome,{2305}Polished Nickel,{2306}A ntique White,{2307}Oil Rubbed Bronze,{23 08}Rust,{2309}Matte Black,{2310}Verde]	Polished Brass	Weathered Bronze	Pewter
3	{123}XYZ: S[{0002}C,{000 3}D,{0005}E,{0006}F,{000 8}H]	{123}XYZ: S[{0000}A,{0001}B,{0002}C,{000 3}D,{0004}A,{0005}E,{0006}F,{0007}G,{000 8}H,{0009}I]	C	E	F	H
Sheet2

Cell Formulas
Range	Formula
A2	A2	=StripCodes(B2,C2:E2)
A3	A3	=StripCodes(B3,C3:F3)

 

[Peter_SSs]

Very neat PGC! However, it does have the drawback of having to specify the exact range of populated cells in columns C, D, E etc in each formula since it fails if there are blank cells at the end.

It also fails if there are blank cells at the start or in the middle of the range. Whilst that may be unlikely, it would certainly be better if the user could use a range of columns wide enough to cater for the greatest number of expected columns and just copy the formula down.

Therefore, I suggest this slight addition to your code to cope with blanks, wherever they occur in the rCodes range.

I've also incorporated the idea of only creating the RegEx object once to help with code speed as we discussed in another thread a couple of months ago.

Function StripCodes(sText As String, rCodes As Range) As String
  Static RegEx As Object
  Dim sCodes As String
  
  If RegEx Is Nothing Then
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
  End If
  
  sCodes = Join(Application.Index(rCodes.Value, 0), "|")
  
  With RegEx
    .Pattern = "\|+(?=\|)|^\||\|$"
    sCodes = .Replace(sCodes, "")
    .Pattern = "\{\d{4}\}(?!" & sCodes & ")[a-zA-Z ]+,?"
    StripCodes = Replace(.Replace(sText, ""), ",]", "]")
  End With
End Function

Formula in A2 copied down.

=StripCodes(B2,C2:H2)

Excel Workbook
	A	B	C	D	E	F	G	H
2	{165}Finishes: S[{2297}Polished Brass,{2302}Weathered Bronze,{2303}Pewter]	{165}Finishes: S[{2297}Polished Brass,{2298}Raw Brass,{2299}Brushed Brass,{2300}Rubbed Brass,{2301}Antique Brass,{2302}Weathered Bronze,{2303}Pewter,{2304}Polished Chrome,{2305}Polished Nickel,{2306}Antique White,{2307}Oil Rubbed Bronze,{2308}Rust,{2309}Matte Black,{2310}Verde]	Polished Brass	Weathered Bronze	Pewter
3	{123}XYZ: S[{0002}C,{0005}E,{0006}F,{0008}H]	{123}XYZ: S[{0000}A,{0001}B,{0002}C,{0003}D,{0004}A,{0005}E,{0006}F,{0007}G,{0008}H,{0009}I]	C	E	F	H
4	{123}XYZ: S[{0005}E,{0006}F]	{123}XYZ: S[{0000}A,{0001}B,{0002}C,{0003}D,{0004}A,{0005}E,{0006}F,{0007}G,{0008}H,{0009}I]		E			F
RegEx

 

[pgc01]

Good points, Peter.

The first pattern does not delete all the "|" at the beginning of the string. For ex. "||a" results in "|a" instead of just "a".

Another option for the first pattern:

        .Pattern = "^\|+|\|+(?=\||$)"

 

[Peter_SSs]

The first pattern does not delete all the "|" at the beginning of the string.

Darn it!
I thought that I had tested all the different possibilities but was probably feeling too excited that I had actually managed to use a 'lookahead' successfully for about the first time.

Anyway, thanks for picking that up and for the replacement pattern.