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)

 

[Peter_SSs]

It is a pity we have had no direct feedback from the OP regarding the original question.
Without knowing what is possible in the A29 & down range & exactly what results would be expected for any varied data we are all guessing a bit, and all suggestions so far have possible "issues" depending on that data as indicated by the highlighted cells below. (BTW, not tested in the OP's Excel 2016, only 365)
I have made one more suggestion in column G.

23 11 24.xlsm
	A	B	C	D	E	F	G
25	op	mk	jk	ju
26	12	15	45	2
27
28		Post 2	Post 3	Post 4	Post 6	Post 10	Post 11
29	op+mk	27	27	27	27	27	27
30	&mk	15	15	15	15	15	15
31	mk+jk&ju	62	17	62	62	62	62
32	qop+jkju	59	#N/A	0	59	59	0
33	mk&jk&mk	60	30	60	75	75	75
Lookup Parts

Cell Formulas
Range	Formula
B29:B33	B29	=SUMPRODUCT(ISNUMBER(SEARCH(A$25:D$25,A29))*A$26:D$26)
C29:C33	C29	=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))
D29:D33	D29	=SUMPRODUCT(--ISNUMBER(SEARCH("+"&A$25:D$25&"+","+"&SUBSTITUTE(A29,"&","+")&"+")),A$26:D$26)
E29:E33	E29	=SUMPRODUCT($A$26:$D$26,(LEN(A29)-LEN(SUBSTITUTE(A29,$A$25:$D$25,"")))/LEN($A$25:$D$25))
F29:F33	F29	=SUMPRODUCT(IFERROR(HLOOKUP(MID(A29,ROW(INDIRECT("1:"&LEN(A29)-1)),2),$A$25:$D$26,2,0),0))
G29:G33	G29	=SUMPRODUCT((LEN("+"&SUBSTITUTE(SUBSTITUTE(A29,"&","+"),"+","++")&"+")-LEN(SUBSTITUTE("+"&SUBSTITUTE(SUBSTITUTE(A29,"&","+"),"+","++")&"+","+"&A$25:D$25&"+","")))/(LEN(A$25:D$25)+2),A$26:D$26)

 

[Rick Rothstein]

I cannot test this because I do not have XL2016 here, but I think it should work (theoretically, it should be able to handle up to 99 delimited terms)...

=SUMPRODUCT(IFERROR(HLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(A35,"&"," "),"+"," ")," ",REPT(" ",300)),ROW(INDIRECT("1:99"))*300,300)),A$31:D$32,2,0),))

If it does not work, try changing the SUMPRODUCT to SUM and then array-enter the formula (using CTRL+SHIFT+ENTER to commit the formula).

NOTE: I have edited the formula above from its original to account for a comment made by @Peter_SSs below. This is what I changed...

Originally: ROW($1:$99)

Now: ROW(INDIRECT("1:99"))

The above formula is now Volatile but it protects against rows being inserted before the formula row. If you are sure you will never insert rows before the formula rows, you could consider going back to what I had originally as formula would not be Volatile.

 

[Peter_SSs]

but I think it should work

.. provided rows are not subsequently added at the top of the worksheet.

 

[Rick Rothstein]

.. provided rows are not subsequently added at the top of the worksheet.

I am guessing from your response that the SUMPRODUCT version worked. I have edited my formula in Message #12 to cure the problem of inserting rows at the top of the worksheet. Thanks for noting the problem and confirming the formula works.

 

[Newbienew]

@Peter_SSs I am currently running Excel 2016, but depending on the user, they could be running any version of Excel. When constructing certain spreadsheets, I have to keep this in mind, as I made the mistake of creating something in a new version but it didn't work on an older version of Excel. Due to various changes of any project I am working on, I try to make things more dynamic or easy to simple to use despite addition changes. Or rather account for issues that may arise thay might call for figuring out a whole new formula.
My range of rows is 145, but additional rows can be added, pending when and if new information is given
I would like to share the whole sheet but XL2BB will not allow me to post something so massive. Instead I posted the isolated issue I had and reconstructed my issue from scratch.

Below is a section out of the 145 rows. In the middle of the formula in column N you will see the last formula I used from this message thread. Your end results in post 11 is correct in the adding of numbers.

ASVAB CAL-WLPRLH247389.xlsb
	B	C	D	E	F	G	H	I	J	K	L	M	N
137	VE	<50	AR	<60	MC	<49	MK	<30	189	108	AR+MK	↑ 100	NOT QUALED 81 & AR+MK↓100
138	VE	<50	MK	<30	MC	<49	CS	<50	179	216	AR+MK	↑ 100	NOT QUALED -37 & AR+MK↓100
139	GS	<58	MC	<49	EI	<58			165	167	AR+MK	↑ 100	NOT QUALED -2 & AR+MK↓100
140	VE	<50	AR	<60	MK	<30	AO	<56	196	216	AR+MK	↑ 100	NOT QUALED -20 & AR+MK↓100
RATINGS (2)

Cell Formulas
Range	Formula
G137:G140,E137:E140,C137:C140,I137:I140	I137	=IF(LEN(H137)=2,1,2)*IFNA(HLOOKUP(RIGHT(H137,2),HOME!$A$2:$L$3,2,0),0)
J137:J140	J137	=C137+E137+G137+I137
N137:N140	N137	=IF(AND(ISBLANK(L137),ISBLANK(M137)),IF(J137>=K137,"QUALED","NOT QUALED "&J137-K137),IF(AND(J137>=K137,SUMPRODUCT(HOME!$A$3:$L$3,(LEN(L137)-LEN(SUBSTITUTE(L137,HOME!$A$2:$L$2,"")))/LEN(HOME!$A$2:$L$2))>=M137),"QUALED","NOT QUALED "&IF(SUMPRODUCT(HOME!$A$3:$L$3,(LEN(L137)-LEN(SUBSTITUTE(L137,HOME!$A$2:$L$2,"")))/LEN(HOME!$A$2:$L$2)) <M137,J137-K137&" & "&L137&"↓"&M137,J137-K137)))

The formula has to be dynamic as there may be an entry in any cell in column L or not, and if there is an entry it needs to be broken down and calculated for a passing score.

After reading your posting and looking over your results, I will have to bring it to my supervisors and pose a question again: "Is there a difference between + and & symbols in these entries ?" If there is a difference, I am going to have to go back to the draw board on how to calculate anything with a + in between it and things with a & to be separate and still be calculated into being qualed or not qualed.

 

[Peter_SSs]

I am currently running Excel 2016, but depending on the user, they could be running any version of Excel. When constructing certain spreadsheets, I have to keep this in mind, as I made the mistake of creating something in a new version but it didn't work on an older version of Excel.

Then, when asking a question that you want to be able to run on several versions, you need to state that up front in post 1 of any thread and also give the oldest version that it needs to run on. Keeping in mind that the older the version that it needs to run on, the less available functions there is likely to be and that any suggestion will be likely more complicated and/or slower-running the older the version has to be.

In the middle of the formula in column N you will see the last formula I used from this message thread.

Does that formula return the results that you want?
Other than that we can't tell a lot about the column N formula since it refers to ranges in 'HOME' that you have not provided an XL2BB sample of

 

[Newbienew]

Most of the people that I work with are running excel version 2016 or greater.

I am currently pending feedback for the implementation of the formula. I believe the results are good. I should get an answer from my supervisors later today or tomorrow.

The home page consists of entry data at the top.

GS	AR	WK	PC	MK	EI	AS	MC	AO	VE	CS	CT
50	50	50	50	50	50	50	50	50	50	50	70

These two rows feed the other sheet and spit out the results. The letters never change only the numbers.

 

[Peter_SSs]

I am currently pending feedback for the implementation of the formula. I believe the results are good. I should get an answer from my supervisors later today or tomorrow.

OK, we will assume everything is working unless we hear from you again.