How to Find and Replace Multiple Values in Excel - All at once

et BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
FindReplaceList = Table.ToRows(FindReplaceTable),
Counter = Table.RowCount(FindReplaceTable),
BulkReplaceValues = (DataTableTemp, n) =>
let
ReplaceTable = Table.ReplaceValue(
DataTableTemp,
if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
Replacer.ReplaceText,
DataTableColumn
)
in
if n = Counter - 1
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n + 1),
Output = BulkReplaceValues(DataTable, 0)
in
Output
in
BulkReplace

I am still getting error ...

THANKS

Great Video Brother and thank you!

Ok

Can this replace vale’s within the cells? For example, if you wanted to replace a certain domain in the list of emails with a different domain.

so much complicated anybody can get error easily

Thx a lot. It helped me too much. Appreciated :)

This worked Thanks so much!

We have error for code Expression.SyntaxError: Token Eof expected.

= BulkR(#"Changed Type",Table2,{"wrong answer"})

Hello
Can you please tell how to search single value from multiple workbooks open at a time.

Hi , How to find different names and replaces in entire workbook in one shot, which are not in any order ?

Hi, do you know why I might be getting an error of "We cannot convert the value "Location" to type List? it says Value=Location Type=[Type]. Thank you

THANKSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS

Thanks alot :)

Correct Query -

let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
FindReplaceList = Table.ToRows(FindReplaceTable),
Counter = Table.RowCount(FindReplaceTable),
BulkReplaceValues = (DataTableTemp, n) =>
let
ReplaceTable = Table.ReplaceValue(
DataTableTemp,
if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
Replacer.ReplaceText,
DataTableColumn
)
in
if n = Counter - 1
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n + 1),
Output = BulkReplaceValues(DataTable, 0)
in
Output
in
BulkReplace