Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Underscore instead of space (2000 SR1)

    Instead of spaces between text in one column, I want underscores. Do I have to type all underscores or is there a way of getting Excel to convert spaces into underscores please?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Underscore instead of space (2000 SR1)

    You can use Edit | Replace...
    Or you can insert an empty column, and fill it with formulas using the SUBSTITUTE function:

    <code>=SUBSTITUTE(A1," ","_")</code>

    where A1 is a cell with text containing spaces. You can then hide the original column, for example.
    Or you can write a macro to replace spaces with underscores.
    You could even let Excel change spaces to underscores automatically when the user finishes editing a cell.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Underscore instead of space (2000 SR1)

    Select the range where you want to apply this change (or don't select a range if you want to change the entire sheet) and go to EditReplace. Put a single space in the Find box and an underscore in the Replace box. Make sure you DO NOT have a checkmark in the "Match Entire Cell Contents" box and click Replace All.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Re: Underscore instead of space (2000 SR1)

    Thanks both for your replies.
    Hans: You say I can let Excel convert spaces to underscores, is that via Autocorrect or is there some other way that I am not seeing please?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Underscore instead of space (2000 SR1)

    You can use the Worksheet_Change event to replace spaces by underscores. In the attached workbook, this is done for all cells in the shaded area. If you want to see the code, right-click the sheet tab and select View Code from the popup menu.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Re: Underscore instead of space (2000 SR1)

    At first I had trouble getting it to work after changing the range to A2:B20000 but after a few attempts it started working!

    It is exactly what I wanted, thank you so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •