Soft Hyphen in Excel

What is a soft hyphen?

I did some research and what I found says it's character 173 so

Range("a1") = "Soft" & Chr(173) & "hyphen"

but that looks like a regular hyphen to me.

A cell which look like this...
Assume A1 is men
|="Now is the time for all good "&A1&" to come to the aid of their country."        |
which yields
|Now is the time for all good men to come to the aid of their country.                  |
If you change A1 to men and women, you will get...
|Now is the time for all good men and women to come to the aid of their           |
|country.                                                                                                                        |
With a soft hyphen in the word "coun-try" you would get...
|Now is the time for all good men and women to come to the aid of their coun-|
|try.                                                                                                                                |

I am not sure where you would insert the formula you are specifying.

Or as a UDF

Function SoftHyphen(r As Range, pos As Integer) As String
SoftHyphen = Left$(r, pos - 1) & Chr(173) & Mid$(r, pos)
End Function

Select allOpen in new window

Usage: =softhyphen(A1,5)

which would change "sometext" to "some-text"

To insert the character in your formula, hold down the Alt key and using the numeric keypad type 0173

After some more research I found this statement

Soft hyphens are always displayed in cells because cells don't wrap their content naturally. They disappear in web pages and when pasted into word processors because their text formatting engines support soft hyphenation.

I verified it by inserting the character in the formula in Excel where it always looks like a regular hyphen, but when the cell content was copied to an enhanced word processor (in other words not Notepad) it was invisible.

You have lost me.  I am enclosing a snippet of the spreadsheet.  If you will look under the "Engagement Letter" tab, first paragraph, 4th line.  The word engagement is where I am wanting to put a soft hyphen.
Snippet24b.xls

Sorry for that.

Do you want the soft hyphen to appear and disappear in Excel? If so that doesn't seem to be possible.

I've attached your workbook after inserting the soft hyphen between "engage" and "ment".
Snippet24b.xls

I am not showing any - etc. in your example.  Could it be because I am using Excel 2003?

I'm attaching a new spreadsheet just in case there was some confusion with the workbook name and you opened your Snippet24b rather than mine. In any case in Excel 2010 the attached workbook shows
which was accomplished as described in post ID: 40859623. But remember that from everything I've seen, the hyphen will always appear in Excel but if the cell contents is copied to an advanced word processor (like Word) it will not appear unless needed.
28693575.xls

The cell copied to Word.

Obviously Excel does not handle soft-hyphens well, if at all. But, you last suggestion solves the problem.  Thanks.

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015