Automatically add underscores between multiple words in a cell

I have a cell with multiple words and I need an underscore between them.

using

=IFERROR(LEFT(C8,MATCH(1,INDEX((MID(C8,ROW(INDIRECT("2:" & LEN(C8)-1)),1)=" ")*(ISERROR(VALUE(MID(C8,ROW(INDIRECT("2:" & LEN(C8)-1))-1,1))))*(ISERROR(VALUE(MID(C8,ROW(INDIRECT("2:" & LEN(C8)-1))+1,1)))),0),0)) & "_" & MID(C8,MATCH(1,INDEX((MID(C8,ROW(INDIRECT("2:" & LEN(C8)-1)),1)=" ")*(ISERROR(VALUE(MID(C8,ROW(INDIRECT("2:" & LEN(C8)-1))-1,1))))*(ISERROR(VALUE(MID(C8,ROW(INDIRECT("2:" & LEN(C8)-1))+1,1)))),0),0)+1,255),C8)

This is only adding the underscore to the first word.

2

1 Answer

If you want to change all spaces for underscores use:

=SUBSTITUTE(C8," ","_")

To remove multiple spaces at the start or end of your string, use:

=SUBSTITUTE(TRIM(C8)," ","_")

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy