Using the Replace command on multiple seperate characters in a single cell

[Garraelius]

I am trying to use the Replace command to change Asterixes (*) (e.g 12*50*5013*6) to "X", I can use th command to change the first asterix by using for example, =REPLACE(E23,3,1,"x"), but despite repeated attempts I cannot expand this to replace the other "*" in the same string of text. I have a spreadsheet with over 17000 of these entries that I need to change and all are differently spaced within the number strings. Any Help appreciated. Many thanks, Garraelius.

 

[NARAYANK991]

Hi ,


Try using the SUBSTITUTE function.


=SUBSTITUTE(E23,"*","X")


will replace all the asterisks with 'X's.


Narayan

 

[bobhc]

Good day Garraelius

Just the thing for find/replace/replace all

 

[Garraelius]

Brilliant!! Thanks a million, I knew there had to be a way

 

[Debraj]

Hi Garraelius,


Just for information, to search for (*) or (?) use a (~) (tilde) character before the search string..


go to Find & Replace

Find what : ~*

Replace With : X


and press Replace All ..

 

[Garraelius]

Thanks so much to all, I can see I will learn lots of new Excell tricks on this Forum, and Thanks to my wife Denice for introducing me to the site