Replace consecutive underscores with a specified set of characters

[sonnaps]

Hello all,

I have an excel sheet with data entered for a card game. I have a bunch of cells with a group of underscores, i.e. "He went to the _______." or "She also went to the __."

I want to be able to replace these sets of underscores with another set that is a specific length. Like instead of 5 underscores here, and 3 there, and 6 there... I want to replace each group with exactly 8 underscores, "________".

Is there an easy way to do this?

Thank you

[JohnTopley]

Try

=A1& " "& B1

A1=Text
B1=8 underscores (________)

[Zer0Cool]

This depends how the text and underscores are set on the sheet. Are the text and underscores in the same cell? Or is it text in a cell, underscores in a cell next to it?

If they are separate find and replace is likely the quick and easy...if the underscores are alone in their own cell

You may also be able to use find and replace. Something like:

Find: *_*
With: ________

However if there is text and underscores mixed, then you will likely want to look at a formula to do it. The example given above would combine text without underscores with the desired number of underscores...it wont fix things for you if there is text and underscores mixed in a cell already.

For that you may want to look at the SUBSTITUTE function. You may also combine something like LEFT/RIGHT/MID with FIND and CONCATENATE. Is the underscoring always at the end of a sentence?

If its always at the end (assuming cell with text and underscore is in B3):

Please Login or Register  to view this content.

If underscoring could be anywhere in the string:

Please Login or Register  to view this content.

Not the most elegant formula, but gets the job done. Essentially I replace the first underscore with a character I know wont be in any string, I picked "*" but you can change to your liking. I then replace the other underscores with nothing. Lastly I replace my special character, "*", with 8 underscores. This would not affect text before/after the underscores in your string, but would also only work if there is one set of underscores in the string.

Ie: Works - "He went to the _____."
Doesnt work - "He went to the _____ and to the _______."

Hopefully this helps

[sonnaps]

It worked! Thank you so much. There was only one or two that had double sets in the same cell. Any other double sets we actually in separate cells in the same row so it still worked. Thanks again!

[Zer0Cool]

If you are satisfied with the results you may consider mentioning what specifically worked. At the very least mark the post solved.

[sonnaps]

If underscoring could be anywhere in the string:

Please Login or Register  to view this content.

This was it, thanks.