时间戳

首页 > 解决方案 > 在字符串末尾添加特定数字，直到达到特定的字符总数

google-sheets - 在字符串末尾添加特定数字，直到达到特定的字符总数

问题描述

我的值示例Column C：

https://www.betfair.com/exchange/plus/football/market/1.186894151
https://www.betfair.com/exchange/plus/football/market/1.186867498
https://www.betfair.com/exchange/plus/football/market/1.1869852
https://www.betfair.com/exchange/plus/football/market/1.186986585
https://www.betfair.com/exchange/plus/football/market/1.186986773
https://www.betfair.com/exchange/plus/football/market/1.18698631
https://www.betfair.com/exchange/plus/football/market/1.186971962
https://www.betfair.com/exchange/plus/football/market/1.18698667
https://www.betfair.com/exchange/plus/football/market/1.186973002

在最后一个之后/将始终具有相同数量的字符。但由于某些莫名其妙的原因，API 提供的值没有右侧存在的零，例如：

如果正确的值为：

1.1011100

交付的价值是：

1.10111

所以要调整它，我需要在右边添加零，因为我用这个公式分隔值的最后一部分：

=ARRAYFORMULA(IF(C1:C="","",RIGHT(C1:C,LEN(C1:C)-FIND("*",SUBSTITUTE(C1:C,"/","*",LEN(C1:C)-LEN(SUBSTITUTE(C1:C,"/","")))))))

结果是：

1.186894151
1.186867498
1.1869852
1.186986585
1.186986773
1.18698631
1.186971962
1.18698667
1.186973002

为了计算可以找到的最大字符数，我使用：

=ARRAYFORMULA(MAX(LEN( FORMULA I PUT A LITTLE ABOVE IN QUESTION )))

这种情况下的结果是，因此在所有值中，最后一个斜杠之后11必须有字符。11

但是现在我不知道如何添加每个值中缺少的零数字，以便结果是：

https://www.betfair.com/exchange/plus/football/market/1.186894151
https://www.betfair.com/exchange/plus/football/market/1.186867498
https://www.betfair.com/exchange/plus/football/market/1.186985200
https://www.betfair.com/exchange/plus/football/market/1.186986585
https://www.betfair.com/exchange/plus/football/market/1.186986773
https://www.betfair.com/exchange/plus/football/market/1.186986310
https://www.betfair.com/exchange/plus/football/market/1.186971962
https://www.betfair.com/exchange/plus/football/market/1.186986670
https://www.betfair.com/exchange/plus/football/market/1.186973002

我应该怎么做才能得到这个结果？

电子表格链接：
https ://docs.google.com/spreadsheets/d/1PPnwwzFzN60G0rUjTgbBn5D5uIXaO-OTJtuHeKxCilw/edit?usp=sharing

标签： google-sheetsgoogle-sheets-formula

解决方案

我在电子表格中为您提供了 2 种方法。

LEN：

=ArrayFormula(IF(C1:C="",,C1:C&REPT("0",F1-LEN(E1:E))))

REGEXEXTRACT：

=ArrayFormula(IF(C1:C="",,REGEXEXTRACT(C1:C&REPT("0",F1),".+\/.{"&F1&"}")))

推荐阅读