Is there a way to mass replace all?

[mikeyates]

First of all, you all have been great. I really appreciate this forum.

We have a 27 question survey that I have to report. We can have up to 45 students. 23 of those 27 questions have 6 options and the last 4 questions are comments.
I have to report this in XML later, but I don't think matters for my question.

I have found a way (thanks to earlier help) to automatically take the evaluations and put them in the report format that I need.
However, I'm trying to fill that on down to the possible 45 students.

Excel wants to assume the next wrong. It wants to go chronologically, and my report doesn't work that way.
Right now, I'm having to remove the =, find and replace all one line at a time. But I'm expecting 1300 lines.

Can I customize the way Excel auto fills?
Can I write a script for a mass replace all?

Just to clarify, Each student is one row, and each question is one column.
I report them one student at a time.

I took a screenshot of two students, one with the = and one without, so that you could see what it's supposed to look like, and what I'm dealing with behind the scenes.
You'll see the pattern of the rows and columns.

Forgive me if this question isn't really an excel question and is more of a bash script question.

 

[mikeyates]

No just once, all you need to change is the Range at the start to get all the rows.

What is the lower case a after the LAMDA?
I'm getting a SPILL result at the moment. I've replaced the referenced cells with the real locations, and have the actual range.
I appreciate your patience with me.

 

[Fluff]

The #spill error means you have cells in he way of the formula. Clear all cells below the formula.
The a is just a variable.

 

[mikeyates]

Wow....That's it! You're amazing. So, now I need to study on the LET command, and LAMBDA

I can't thank you enough.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

This will handle the brackets in the last 4 answers

=LET(data,G3:FG29,r,ROWS(data),TOCOL(HSTACK(EXPAND("<evaldata>",r,,"<evaldata>"),MAP(FILTER(data,LEFT(G2:FG2,6)="answer"),MOD(SEQUENCE(r,27,0),27)+1,LAMBDA(a,b,"    <question id="&CHAR(34)&b&CHAR(34)&" answer="&CHAR(34)&IF(b<24,LEFT(TEXTAFTER(a,"(")),a)&"/>")),EXPAND("</evaldata>",ROWS(data),,"</evaldata>"))))

 

[mikeyates]

This will handle the brackets in the last 4 answers

=LET(data,G3:FG29,r,ROWS(data),TOCOL(HSTACK(EXPAND("<evaldata>",r,,"<evaldata>"),MAP(FILTER(data,LEFT(G2:FG2,6)="answer"),MOD(SEQUENCE(r,27,0),27)+1,LAMBDA(a,b,"    <question id="&CHAR(34)&b&CHAR(34)&" answer="&CHAR(34)&IF(b<24,LEFT(TEXTAFTER(a,"(")),a)&"/>")),EXPAND("</evaldata>",ROWS(data),,"</evaldata>"))))

You mentioned that earlier, but it was populating the last questions already. I didn't notice anything missing?

 

[Fluff]

On row 27 of the sample the last answer should be <question id="27" answer="Comprehensive Cybersecurity Defense (CCD)./> but was coming out as <question id="27" answer="C/>
Any answer that had brackets was wrong, hence the change.

 

[mikeyates]

Simply, amazing. Where may I sing your praises?

 

[mikeyates]

On row 27 of the sample the last answer should be <question id="27" answer="Comprehensive Cybersecurity Defense (CCD)./> but was coming out as <question id="27" answer="C/>
Any answer that had brackets was wrong, hence the change.

I hate to bother you again, but I did come across a problem. I understand that it's my problem, but I was hoping for help one more time.

The last four questions are comments, and the system that I report to just threw a hissy fit when I tried submitting them as "questions". Rather, they are supposed to be called comments. Here's what it looks like in the XML program.

Any suggestions how to break up the last four of every set?

 

[Fluff]

How about

=LET(data,G3:FG29,r,ROWS(data),TOCOL(HSTACK(EXPAND("<evaldata>",r,,"<evaldata>"),MAP(FILTER(data,LEFT(G2:FG2,6)="answer"),MOD(SEQUENCE(r,27,0),27)+1,LAMBDA(a,b,IF(b<24,"    <question id=","    <comment id=")&CHAR(34)&b&CHAR(34)&" answer="&CHAR(34)&IF(b<24,LEFT(TEXTAFTER(a,"(")),a)&"/>")),EXPAND("</evaldata>",ROWS(data),,"</evaldata>"))))