Add value to different integers in same cell in MS Excel-CodePudding

I have a typical book index in a spreadsheet that goes like this:

enter image description here

I need Excel to recognize the different integers in the same cell and add a constant each one of them.

Let's say the constant is 5

The result should be like this:

enter image description here

I couldn't get excel to recognize the different values in a cell.

CodePudding user response：

Not sure if this could be done smoothly and without using long and complex formulas.

Why don't you try to split the cell into multiple columns (Data > Data Tools > Text To Columns > Delimited and select Comma as delimiter). Once you have all different values is separate columns, it would be easy to apply formulas and add the constant. After that, you could use CONCATENATE formula to merge texts and numbers back to single value in a single column.

Hope this helps.

CodePudding user response：

My two cents, assuming Excel ms365:

Formula in B1:

=BYROW(A1:A3,LAMBDA(a,LET(X,TEXTSPLIT(a,", "),TEXTJOIN(", ",,IFERROR(X C1,X)))))

Or, if no access to TEXTSPLIT(), then use:

=BYROW(A1:A3,LAMBDA(a,LET(X,FILTERXML("<t><s>"&SUBSTITUTE(a,",","</s><s>")&"</s></t>","//s"),TEXTJOIN(", ",,IFERROR(X C1,X)))))

Or, if no access to BYROW(), you'd have to drag the following:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"),TEXTJOIN(", ",,IFERROR(X C$1,X)))

CodePudding user response：

This is what I have tried,

Formula Applicable To Only O365 Beta Channel (Insiders).

• Formula used in cell B1

=TEXTJOIN(", ",,TEXTBEFORE(A1,", ",1),TEXTSPLIT(TEXTAFTER(A1,", ",1),", ") 5)

Or, if you are not using the above Excel Version, but using either Excel 2019, 2021 or Regular O365, then,

• Formula used in cell B1

=TEXTJOIN(", ",,LEFT(A1,FIND(",",A1)-1),IFERROR(
FILTERXML("<a><b>"&SUBSTITUTE(A1,", ","</b><b>")&"</b></a>","//b") 5,""))

Edit

One improvised approach:

=TEXTJOIN(", ",,TEXTBEFORE(A1,", ",1),IFERROR(TEXTSPLIT(A1,", ") 5,""))

You can also accomplish this task quite easily with Power Query.

CodePudding user response：

I would separate the text and each value into separate cells, then use & to combine into the format you want, something like so:

=A1&", "&B1 G1&", "&C1 G1&", "&D1 G1

where A1 contains "Trauma" and B1 contains 15, C1 contains 17 and D1 contains 25. G1 holds the constant 5.

See