How to substitute out spaces

[Cindy]

If we have column A with "Bob_Jones" "Big_Bob_Jones" and "Little_Bob_" and want to create a list to takeout all the Bob's....what is the best way to embed the Substitute formula? (underscore represents Spaces)

 

[Smallman]

Hi Cindy

Give this a try. Where A1 represents your string.

=SUBSTITUTE(A1,"Bob ","")

File attached to show workings.

Take care

Smallman

 

[Khalid NGO]

Hi Cindy,
Further to Smallman's answer, you can use the bellow formula too:

=TRIM(SUBSTITUTE(A1,"Bob ",""))

This will remove extra spaces between strings.