Soft Hyphen in Excel

Is there anyway to do a soft hyphen in text in an Excel cell?
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
What is a soft hyphen?
Martin LissOlder than dirtCommented:
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.
Bill GoldenExecutive Managing MemberAuthor Commented:
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.                                                                                                                                |
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Bill GoldenExecutive Managing MemberAuthor Commented:
I am not sure where you would insert the formula you are specifying.
Martin LissOlder than dirtCommented:
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

Open in new window

Usage: =softhyphen(A1,5)

which would change "sometext" to "some-text"
Martin LissOlder than dirtCommented:
To insert the character in your formula, hold down the Alt key and using the numeric keypad type 0173
Martin LissOlder than dirtCommented:
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.
Bill GoldenExecutive Managing MemberAuthor Commented:
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.
Martin LissOlder than dirtCommented:
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".
Bill GoldenExecutive Managing MemberAuthor Commented:
I am not showing any - etc. in your example.  Could it be because I am using Excel 2003?
Martin LissOlder than dirtCommented:
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
soft hyphenwhich 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.
Martin LissOlder than dirtCommented:
The cell copied to Word.
Martin LissOlder than dirtCommented:
BTW if all you want to do is to "re-wrap" the cell after a change while keeping the cell width, you can do this (which works in the range D28:D200).

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Cells.Count = 1 Then
    If Not Intersect(Target, ActiveSheet.Range("D28:D200")) Is Nothing Then
        Target.WrapText = True
        If Target.WrapText Then
        End If
    End If
End If

Application.ScreenUpdating = True

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
Obviously Excel does not handle soft-hyphens well, if at all. But, you last suggestion solves the problem.  Thanks.
Martin LissOlder than dirtCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.