# Soft Hyphen in Excel

Is there anyway to do a soft hyphen in text in an Excel cell?
LVL 1
###### Who is Participating?

x
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.

Older than dirtCommented:
What is a soft hyphen?
Older 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.
Executive 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.                                                                                                                                |
Executive Managing MemberAuthor Commented:
I am not sure where you would insert the formula you are specifying.
Older 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
``````

Usage: =softhyphen(A1,5)

which would change "sometext" to "some-text"
Older than dirtCommented:
To insert the character in your formula, hold down the Alt key and using the numeric keypad type 0173
Older 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.
Executive 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.
Snippet24b.xls
Older 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".
Snippet24b.xls
Executive Managing MemberAuthor Commented:
I am not showing any - etc. in your example.  Could it be because I am using Excel 2003?
Older 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
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
Older than dirtCommented:
The cell copied to Word.
Older 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
Target.Rows.AutoFit
End If
End If
End If

Application.ScreenUpdating = True

End Sub
``````

Experts Exchange Solution brought to you by

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

Executive Managing MemberAuthor Commented:
Obviously Excel does not handle soft-hyphens well, if at all. But, you last suggestion solves the problem.  Thanks.
Older 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.