Excel查找和替换公式中的文本。

I have 3 columns: A and B contain numbers, and C has a html link. I want to replace numbers in the html link with the numbers from columns A & B.

我有3列:A和B包含数字，C有一个html链接。我想把html链接中的数字替换为A和B列中的数字。

Can anyone tell me how I can do this? I tried using the replace function, but it doesnt work for multiple variables.

有人能告诉我怎么做吗?我试过使用替换函数，但它不适用于多个变量。

Example: random.com/do?ID=1111&SourceID=2222 I'd replace 1111 with ID columan A 617 and 2222 with sourceID column B 2

例如:random.com/do?ID=1111& sourceID =2222我将用ID columan A 617和2222替换为sourceID列B 2

SourceID LocationID link
617     2           random.com/do?ID=1111&SourceID=2222 
1878     39         random.com/do?ID=1111&SourceID=2222  
4148     48         random.com/do?ID=1111&SourceID=2222

1 个解决方案

#1

I would not use the replace formula here because it requires a "position and length" which will change once you update the link with a new value from SourceID or LocationID and the logic to figure out the new location is overkill and not needed.

我不会在这里使用替换公式，因为它需要一个“位置和长度”，当您用SourceID或LocationID的新值更新链接时，该值将会更改，而计算新位置的逻辑是“超杀”的，不需要。

Instead you can use the Substitute formula instead which I think is more readable and easier to implement. This formula replaces "text" with "text"

相反，你可以使用替代公式，我认为它更易于阅读和实现。这个公式将“text”替换为“text”

If you use 2 Substitutes (1 outer and 1 nested) you can replace both strings in one line. Try putting this in column D

如果您使用2个替代品(1个外层和1个嵌套)，您可以在一行中替换这两个字符串。试着把它放到D列

=SUBSTITUTE(SUBSTITUTE(C1,"1111",A1),"2222",B1)

and you can auto-fill down

你可以自动填写。

#1