substitute function

[macky]

Sample worksheet :

a | b | c |
1 xa xb xc
2 xc xx xa
3 xx xx xx

using

substitute(a1,"a","s") works for me

my goal is...

to use the function to check for the entire sheet and
replace all matching characters with replacement
characters.

anybody can help me with this one?

tnx

 

[Frank Kabel]

Hi
I would use 'Edit - Replace' for this

 

[macky]

thanks Frank.

Edit - replace works fine.

but what if i'm trying to
replace char(10) embedded on the worksheet?

this doesnt work on find and replace.

 

[Ken Wright]

Assumimg data as written, with all values to be replaced being the last
character in the string, and with your table of values and corresponding
replacement values named MyTable, and with all your data being on Sheet1, in
cell A1 on any other sheet, put the following formula and copy down and across
as necessary.

=LEFT(Sheet1!A1,LEN(Sheet1!A1)-1)&VLOOKUP(RIGHT(Sheet1!A1),MyTable,2,0)

 

[Ken Wright]

Edit / Replace / Replace what:- Hold down the ALT key and type 0010 on the NUM
pad at the right of your keyboard

or just copy one and paste it into the Replace what box