Add value to different integers in same cell in MS Excel

user_18657599 0
Viewed 160

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.

spreadsheet excel-formula excel
4 Answers

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.

user_99363370

My two cents, assuming Excel ms365:

enter image description here

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)))
user_97581940

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 enter image description here

user_49617000

This is what I have tried,

FORMULA_SOLUTION

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_SOLUTION

• 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.

user_81625200
Related