CODEWITHSUNDEEP
excelexcel-formulaspreadsheet
cilginorlando
cilginorlando

Reputation: 13

Add value to different integers in same cell in MS Excel

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.

Upvotes: 1

Views: 262

Answers (4)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

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.

Upvotes: 0

Solar Mike
Solar Mike

Reputation: 8375

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

Upvotes: 0

JvdV
JvdV

Reputation: 75840

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

Upvotes: 1

Veljko Jovanovic
Veljko Jovanovic

Reputation: 69

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.

Upvotes: 1

Related Questions