CODEWITHSUNDEEP
google-sheets
viewtifuljoe
viewtifuljoe

Reputation: 11

Add a text string before and after any value in a text string appears in Google Sheets

Question!

I have a few hundred cells with text strings that also contain numbers within them. I want with a formula to add text before and after each presence of a number.

So for example: "Find 2 dogs, 3 times". I'd want to turn this to

"Find [insertedtextbefore]2[insertedtextafter] dogs', [insertedtextbefore]3[insertedtextafter] times".

Thanks!

Upvotes: 1

Views: 1006

Answers (1)

Tanaike
Tanaike

Reputation: 201613

  • You want to put [insertedtextbefore] to before the number in the text of the cell.
  • You want to put [insertedtextafter] to after the number in the text of the cell.

  • As a sample value, you want to achieve as follows.

    • From

      Find 2 dogs, 3 times

    • To

      Find [insertedtextbefore]2[insertedtextafter] dogs, [insertedtextbefore]3[insertedtextafter] times

  • You want to achieve this using the built-in functions of Google Spreadsheet.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Sample formula:

=ARRAYFORMULA(REGEXREPLACE(A1:A5," (\d+) ", " [insertedtextbefore]$1[insertedtextafter] "))
  • In this case, the values are put to the cells "A1:A5".

Result:

enter image description here

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 2

Related Questions