Divide (and replace) numbers extracted from a string in Google Sheets

user_4726667 0
Viewed 102

I'm trying to convert numbers that were previously percentages to a decimal format by dividing them by 100 in Google Sheets. Basically, I have:

<polygon points="48, 6, 43, 7, 38, 9, 34, 12, 29, 16, 24, 22, 22, 30, 22, 44, 23, 50, 23, 65, 25, 72, 28, 77, 32, 82, 35, 86, 40, 90, 43, 92, 50, 93, 55, 91, 62, 87, 70, 76, 74, 69, 75, 64, 75, 54, 74, 49, 74, 40, 74, 32, 71, 23, 66, 15, 59, 9, 53, 6" />

And I want:

<polygon points=".48, .06, .43, .07, .38, .09, .34, .12, .29, .16, .24, .22, .22, .30, .22, .44, .23, .50, .23, .65, .25, .72, .28, .77, .32, .82, .35, .86, .40, .90, .43, .92, .50, .93, .55, .91, .62, .87, .70, .76, .74, .69, .75, .64, .75, .54, .74, .49, .74, .40, .74, .32, .71, .23, .66, .15, .59, .09, .53, .06" />

Is there any way to extract numbers, do an operation on them, then replace them in the previous string? I tried to use a regex token in REGEXREPLACE but it doesn't seem to be supported.

=(REGEXREPLACE(A2,"[^[:digit:]]",($/10)))
google-sheets-formula token google-sheets replace regex
1 Answers

You cannot apply any function to the string replacement pattern in REGEXREPLACE. In this concrete case, you may simply append a 0 before single-digit numbers and then add dots before each sequence of 1 or more digits:

=REGEXREPLACE(REGEXREPLACE(A1,"\b\d\b", "0$0"), "\d+", ".$0")

See screenshot:

enter image description here

NOTES:

  • REGEXREPLACE(A1,"\b\d\b", "0$0") - finds a digit not preceded nor followed with a letter/digit/_, and adds a 0 in front of it ($0 is the placeholder for the whole match)
  • REGEXREPLACE(..., "\d+", ".$0") - prepends one or more digit chunks with a dot.
user_38329700
Related