Hi! I am working on a spreadsheet where I have 5 columns of time. I need to
reformat all 5 columns to be the same. My time reads for example, 11:55 -
12:45. I want to reformat the cell so that there is a space before and after
the hyphen. Is there a formula to help me with this? I really don't want to
have to go to each cell and add those spaces.

Please help!

You can do this quickly with 3 Find&Replace operations. Do not include
quotation marks in actual find/replace.

Find:
" -"
Replace:
""

Find:
"- "
Replace:
""

Find:
"-"
Replace:
" - "
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Smiley" wrote:

Hi! I am working on a spreadsheet where I have 5 columns of time. I need to
reformat all 5 columns to be the same. My time reads for example, 11:55 -
12:45. I want to reformat the cell so that there is a space before and after
the hyphen. Is there a formula to help me with this? I really don't want to
have to go to each cell and add those spaces.

Please help!

Hi,
Hit CTRL H, find what type -
replace with :space - space
Replace

"Smiley" wrote:

Hi! I am working on a spreadsheet where I have 5 columns of time. I need to
reformat all 5 columns to be the same. My time reads for example, 11:55 -
12:45. I want to reformat the cell so that there is a space before and after
the hyphen. Is there a formula to help me with this? I really don't want to
have to go to each cell and add those spaces.

Please help!

If you want to do it with a formula (as you requested), try this one...

=SUBSTITUTE(SUBSTITUTE(A1," ",""),"-"," - ")

If you want to do it with Excel's function, I would suggest first replacing
the space character with nothing and then replacing the dash with space
dash space.

--
Rick (MVP - Excel)


"Smiley" wrote in message
...
Hi! I am working on a spreadsheet where I have 5 columns of time. I need
to
reformat all 5 columns to be the same. My time reads for example, 11:55 -
12:45. I want to reformat the cell so that there is a space before and
after
the hyphen. Is there a formula to help me with this? I really don't want
to
have to go to each cell and add those spaces.

Please help!

Hi Luke - Am I doing something wrong? I am leaving the replace portion blank
& the spreadsheet is not changing. Also, I realized that I have hypens in
other areas of my spreadsheet - oops! Is this still doable?

"Luke M" wrote:

You can do this quickly with 3 Find&Replace operations. Do not include
quotation marks in actual find/replace.

Find:
" -"
Replace:
""

Find:
"- "
Replace:
""

Find:
"-"
Replace:
" - "
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Smiley" wrote:

Hi! I am working on a spreadsheet where I have 5 columns of time. I need to
reformat all 5 columns to be the same. My time reads for example, 11:55 -
12:45. I want to reformat the cell so that there is a space before and after
the hyphen. Is there a formula to help me with this? I really don't want to
have to go to each cell and add those spaces.

Please help!

You need to select your cells first, then call up the Edit/Replace dialog.
You can do the procedure in one less step using the method I outlined in my
first posting to this thread (it will also handle cases where there are lots
of extra spaces around the dash as well).

--
Rick (MVP - Excel)


"Smiley" wrote in message
...
Hi Luke - Am I doing something wrong? I am leaving the replace portion
blank
& the spreadsheet is not changing. Also, I realized that I have hypens in
other areas of my spreadsheet - oops! Is this still doable?

"Luke M" wrote:

You can do this quickly with 3 Find&Replace operations. Do not include
quotation marks in actual find/replace.

Find:
" -"
Replace:
""

Find:
"- "
Replace:
""

Find:
"-"
Replace:
" - "
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Smiley" wrote:

Hi! I am working on a spreadsheet where I have 5 columns of time. I
need to
reformat all 5 columns to be the same. My time reads for example,
11:55 -
12:45. I want to reformat the cell so that there is a space before and
after
the hyphen. Is there a formula to help me with this? I really don't
want to
have to go to each cell and add those spaces.

Please help!