Using Substitute or Replace to change specified characters to characters in another cell in a specific order Excel

0 votes
If you will, consider that cell A1 has the following information: A fantastic band was Abba.

Cell 2 should utilise Replace and/or Substitute the six instances of an in cell 3 with the replacements.

What's in Cell 3 is as follows: 123456

So, cell 2 should appear and read: 1bb2 w3s 4 gre5t b6nd.

I have not yet discovered anything that enables me to achieve this. I may display up to "19 of the results" in a single cell. I estimate that I can extract each character from a 60-cell system using left, check them, and then use left on the replacement item to arrange it in sequence, but it sounds like an incredibly laborious process.
Apr 3 in Others by Kithuzzz
• 38,010 points
73 views

1 answer to this question.

0 votes

Replace Occurrences of a Substring With a Sequence of Characters

  • If the setup is always so convenient, you could use:
=LET(d,A1,s,C1,dl,"a",
TEXTJOIN(MID(s,SEQUENCE(,LEN(s)),1),0,TEXTSPLIT(d,dl,,,1)))

... which is short for (less readable than):

=LET(d,A1,s,C1,dl,"a",
   se,MID(s,SEQUENCE(,LEN(s)),1),
   sp,TEXTSPLIT(d,dl,,,1),
TEXTJOIN(se,0,sp))
  • If you want to replace each substring ("a") with an integer sequence, see the shortest formula in JvdV's answer.
answered Apr 3 by narikkadan
• 63,040 points

Related Questions In Others

0 votes
1 answer

Activating a Specific Cell in Excel Using VBA Results to Error 400

I think you trying to select cells(4, ...READ MORE

answered Dec 27, 2022 in Others by narikkadan
• 63,040 points
184 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,040 points
163 views
0 votes
1 answer

Add a shape to a specific cell in Excel

When you use SORT() or data->sort on ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,040 points
444 views
0 votes
1 answer
0 votes
1 answer

Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 22,980 points
1,286 views
0 votes
1 answer

Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,040 points
199 views
0 votes
1 answer

How to remove data within a multi-line cell that comes after a few line breaks

find() return the starting position of a ...READ MORE

answered Jan 26 in Others by narikkadan
• 63,040 points
181 views
+1 vote
2 answers

How to replace a value in a data frame based on a conditional 'If' statement?

It's easier to convert alpha to characters ...READ MORE

answered Jun 6, 2018 in Data Analytics by Sahiti
• 6,360 points
35,867 views
0 votes
1 answer

Using office scripts to read superScripts characters from a cell in excel online

My understanding is that superscript and subscript ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,040 points
244 views
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,040 points
934 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP