Remove ALL occurrences of values [?], {?}, (?) and ?^ from a cell

[Peptide]

Hello!

I am looking for an excel formula (not VBA) to remove all occurrences of values [?], {?}, (?) and ?^ from A1

Value in A1: hello[bob]car[army]is{hope}bicyclez^red(or)blue

Value in B1 would be: [bob]
Value in B2 would be: [army]
Value in B3 would be: {hope}
Value in B4 would be" z^
Value in B5 would be" (or)

Value in C1 would be the value of A1 minus all the values listed on B1:B5 or hellocarisbicycleredblue

FYI: The can be multiple digits in the [ ] or { } or ( ). For the ?^, there is only 1 digit followed by the ^

Thanks in advance if you are up for the challenge!!

 

[awoohaw]

I can just give you the C1 formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(91),""),CHAR(93),""),CHAR(123),""),CHAR(125),""),CHAR(94),""),CHAR(40),""),CHAR(41),"")

To get the other parsed values I think a user will need Excel 365 or 2021 and greater, as the user can use the LAMBDA recursive feature. Are all cells in your project with this structure the exact same word lengths between the non alpha characters?

 

[Anonymous1378]

Try something like

=REDUCE(A1,B1:B5,LAMBDA(a,b,SUBSTITUTE(a,b,"")))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peptide]

I can just give you the C1 formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(91),""),CHAR(93),""),CHAR(123),""),CHAR(125),""),CHAR(94),""),CHAR(40),""),CHAR(41),"")

To get the other parsed values I think a user will need Excel 365 or 2021 and greater, as the user can use the LAMBDA recursive feature. Are all cells in your project with this structure the exact same word lengths between the non alpha characters?

Thank you for your reply. In regards to your question about the exact same word lengths between the alphas, the answer is no. They are generally between 1 and 8 different characters, (sometimes 10). I also updated my profile to say I have Office 365 online and desktop too. FYI - on the ?^, there is always just one character and then the ^. Hope this helps.

 

[Fluff]

I also updated my profile

Thanks for that.
Do you have the values in col B? If not do you want them, or do you just want the value in C1?