Joining two cells with word and numbers without splitting-CodePudding

The title is a bit vague. I will try to explain myself as good as possible.

Let us have for examples two cells: A1 and B1 with the following information.

A1 = Fender/spielt/Gitarre. & B1 = 1|2|5

Is there a possible formula that splits and combines these into words followed by their corresponding number? Like in the next image:

The way I do it right now is by splitting A1 and B1 and adding both cells containing the first word with the first number, et cetera. I think it could go quicker/easier, but I don't know how.

CodePudding user response：

In your situation, how about the following sample formula?

Sample formula:

=TEXTJOIN(" ",TRUE,ARRAYFORMULA(SPLIT(A1,"/")&SPLIT(B1,"|")))

But, from could go quicker/easier, in this case, I thought that when Google Apps Script is used, the process cost might be able to be reduced. In this case, how about the following sample script? When this script is run, please copy and paste the following script to the script editor of Spreadsheet and save the script.

const SAMPLE = v =>
  v.map(([a, b]) => {
    const t = b.split("|");
    return a.split("/").map((e, i) => e   (t[i] || "")).join(" ");
  });

And, please put a custom function of =SAMPLE(A1:B1) to a cell. When you want to convert this for all rows of columns "A" and "B", please put a custom function of =SAMPLE(A:B) to a cell.

Testing:

When the above sample formula is used, the following result is obtained.

When the above sample script is used, the following result is obtained.

Sample formula:

Testing:

References: