Replace multiple underscores with fixed number of underscore

[aghaffar82]

Hello everyone,

I have a long list which contains text with underscores where some are 3-4 at one place within one cell and at some places there are more than 5:

[TABLE="width: 435"]
<tbody>[TR]
[TD="width: 435"]I ___ like to make a ________ for 4, please.[/TD]
[/TR]
</tbody>[/TABLE]

I want to find and replace them all with 5 underscores like this:

[TABLE="width: 435"]
<tbody>[TR]
[TD="width: 435"]I _____ like to make a _____ for 4, please.[/TD]
[/TR]
</tbody>[/TABLE]

The list is too long to be replaced manually, any formula, tip, trick would be very much appreciated.

 

[Fluff]

Probably an easier way, but how about
=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"_","| |"),"||","")),"| |",REPT("_",5))

 

[aghaffar82]

Probably an easier way, but how about
=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"_","| |"),"||","")),"| |",REPT("_",5))

Thank you very much, it works wonders

 

[Fluff]

You're welcome & thanks for the feedback