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, 2023 in Others by Kithuzzz
• 38,020 points
409 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, 2023 by narikkadan
• 63,720 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,720 points
480 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,720 points
660 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,720 points
1,337 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
• 23,260 points
2,222 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,720 points
643 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, 2023 in Others by narikkadan
• 63,720 points
837 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,370 points
36,590 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,720 points
731 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,720 points
2,256 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