Joining two cells with word and numbers without splitting

user_13884438 0
Viewed 28

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:

enter image description here

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.

google-sheets-formula google-sheets
1 Answers

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.

enter image description here

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

enter image description here

References:

user_71086530
Related