Dismissing special characters in a text string

[Newbienew]

So my dilemma is, I have 1 cell with a text string that contains special characters, such as, & or +. My current formula is tied in with a hlookup and use the left and right formula to achieve my goal. But there are other variations that this does not work on. I would like for the Hlookup formula I have to return the added results with out the special characters and whatever is in the cell. I hope below displays the issues I am facing.

Awards Stats.xlsx
	A	B	C	D
25	op	mk	jk	ju
26	12	15	45	2
27
28
29	op+mk	27
30	&mk	#N/A
31	mk+jk&ju	62
32
Sheet1

Cell Formulas
Range	Formula
B29	B29	=HLOOKUP(LEFT(A29,2),A25:D26,2,FALSE)+HLOOKUP(RIGHT(A29,2),A25:D26,2,FALSE)
B30	B30	=HLOOKUP(LEFT(A30,2),A25:D26,2,FALSE)+HLOOKUP(RIGHT(A30,2),A25:D26,2,FALSE)
B31	B31	=HLOOKUP(LEFT(A31,2),A25:D26,2,FALSE)+HLOOKUP(MID(A31,4,2),A25:D26,2,FALSE)+HLOOKUP(RIGHT(A31,2),A25:D26,2,FALSE)

 

[StephenCrump]

This will work for the example provided (SEARCH assumes you don't need case sensitive?) but will quickly break down if the data isn't this simple, e.g. as shown in red.

	A	B	C	D
25	op	mk	jk	ju
26	12	15	45	2
27
28
29	op+mk	27
30	&mk	15
31	mk+jk&ju	62
32	qop+jkju	59
Sheet1

Cell Formulas
Range	Formula
B29:B32	B29	=SUMPRODUCT(ISNUMBER(SEARCH(A$25:D$25,A29))*A$26:D$26)

If your data is more complicated, perhaps you could post some more examples, and let us know whether the only special characters will be & or +

 

[DanteAmor]

Could be

varios 23nov2023.xlsm
	A	B	C	D
25	op	mk	jk	ju
26	12	15	45	2
27
28
29	op+mk	27
30	&mk	15
31	mk+jk&ju	17
Hoja3

Cell Formulas
Range	Formula
B29:B31	B29	=HLOOKUP(LEFT(SUBSTITUTE(SUBSTITUTE(A29,"&",""),"+",""),2),$A$25:$D$26,2,0)+IF(LEN(A29)>3,HLOOKUP(RIGHT(A29,2),$A$25:$D$26,2,0))

(edit)

 

[Peter_SSs]

Taking @StephenCrump's idea just a little further, you could try this.
If there are more special characters, then more SUBSTITUTE's would be required.
This would still not return the correct result if the row 25 values could occur multiple times - see row 33 where if this data is possible I assume that the result should be 75 not 60

BTW, can you please confirm whether you are still using Excel 2016?

23 11 24.xlsm
	A	B	C	D
25	op	mk	jk	ju
26	12	15	45	2
27
28
29	op+mk	27
30	&mk	15
31	mk+jk&ju	62
32	qop+jkju	0
33	mk&jk&mk	60
Lookup Parts

Cell Formulas
Range	Formula
B29:B33	B29	=SUMPRODUCT(--ISNUMBER(SEARCH("+"&A$25:D$25&"+","+"&SUBSTITUTE(A29,"&","+")&"+")),A$26:D$26)

 

[bebo021999]

Might the data have other operator like, +-*/ ? (add, subtract, multiply, divide)

for example:
=op*mk

In that case I suggest an UDF (with VBA code)

 

[Phuoc]

Maybe like this:

Book1
	A	B	C	D
25	op	mk	jk	ju
26	12	15	45	2
27
28
29	op+mk	27
30	&mk	15
31	mk+jk&ju	62
32	mk&jk&mk	75
Sheet1

Cell Formulas
Range	Formula
B29:B32	B29	=SUMPRODUCT($A$26:$D$26,(LEN(A29)-LEN(SUBSTITUTE(A29,$A$25:$D$25,"")))/LEN($A$25:$D$25))

 

[Newbienew]

Maybe like this:

Book1
	A	B	C	D
25	op	mk	jk	ju
26	12	15	45	2
27
28
29	op+mk	27
30	&mk	15
31	mk+jk&ju	62
32	mk&jk&mk	75
Sheet1

Cell Formulas
Range	Formula
B29:B32	B29	=SUMPRODUCT($A$26:$D$26,(LEN(A29)-LEN(SUBSTITUTE(A29,$A$25:$D$25,"")))/LEN($A$25:$D$25))

I would like to use your particular formula as a condition format, but having an issue applying it to an array. When I do it in a sample size, all of them turn red per not meeting the condition versus just the one. Then, when I use a larger array it seems not to work. This depends on where the $ is from what I can tell.

this is the CF formula =IF(SUMPRODUCT(HOME!$A3:$L3,(LEN($L5+$A$3:$X$149)-LEN(SUBSTITUTE($L5,HOME!$A2:$L2,"")))/LEN(HOME!$A2:$L2))<M5,"TRUE","")
The array is =$L$5:$M$150
The color is Red.

I am trying to apply a general CF verse row specific.
What can I do to make this work

 

[Peter_SSs]

I would like to use your particular formula as a condition format,

What does this have to do with conditional format??
This seems unrelated to your question in post #1

 

[Newbienew]

What does this have to do with conditional format??
This seems unrelated to your question in post #1

I shall make a new posting in regards to my question.

 

[Scott Huish]

Not regarding the CF question, perhaps:

=SUMPRODUCT(IFERROR(HLOOKUP(MID(A29,ROW(INDIRECT("1:"&LEN(A29)-1)),2),$A$25:$D$26,2,0),0))

Book1
	A	B	C	D
25	op	mk	jk	ju
26	12	15	45	2
27
28
29	op+mk	27
30	&mk	15
31	mk+mk	30
32	ju&mk	17
Sheet1

Cell Formulas
Range	Formula
B29:B32	B29	=SUMPRODUCT(IFERROR(HLOOKUP(MID(A29,ROW(INDIRECT("1:"&LEN(A29)-1)),2),$A$25:$D$26,2,0),0))