How can I format a character string in Excel to insert hyphens?

I have a string in a cell that is 8 characters. E.g. ABCDEFGH, can I use a custom number format for the cell to have it displayed as AB-CD-EF-GH?

Currently my method is to have another cell reference it and use a formula like

=LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2).

3 Answers

As indicated in the comments, there are no custom number format codes available to do what you want. The only option you have is what you are doing.

For details on custom number format codes and how they work, you can go to this Microsoft webpage.

With Office 365 Excel one can use the TEXTJOIN Function as an Array Formula:

=TEXTJOIN("-",TRUE,MID(A1,(ROW($AAB$1:$AAB$4)-1)*2+1,2))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

The Mid creates an array of the letters in groups of two and the TEXTJOIN joins them back together with a - between them.

Since Excel doesn't provides any Custom format, so that I would like to suggest you VBA (Macro) code, the fastest method to put the required character between Cell values in regular interval.

Sub InsertCharacter()
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
Dim xRow As Integer
Dim xChar As String
Dim index As Integer
Dim arr As Variant
Dim xValue As String
Dim outValue As String
Dim xNum As Integer
xTitleId = "Put Dashes"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
xRow = Application.InputBox("Number of characters :", xTitleId, Type:=1)
xChar = Application.InputBox("Specify a character :", xTitleId, Type:=2)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
xNum = 1
For Each Rng In InputRng xValue = Rng.Value outValue = "" For index = 1 To VBA.Len(xValue) If index Mod xRow = 0 And index <> VBA.Len(xValue) Then outValue = outValue + VBA.Mid(xValue, index, 1) + xChar Else outValue = outValue + VBA.Mid(xValue, index, 1) End If Next OutRng.Cells(xNum, 1).Value = outValue xNum = xNum + 1
Next
End Sub

How it works:

1. Copy & Paste this code as Standard Module with the Sheet.

2. As soon you RUN the Macro, it will pop up 4 INPUT BOX one after other.

   • First Input box will ask for Source Data Range.

   • Second will look for Number Of Characters (after how many characters you need - sign, in your case is 2).

   • Third will ask for the Character to be inserted (in your case is - sign).

   • Final one will ask for Output Cell position (must be a single cell).

3. Finish with OK.

N.B.

This Macro is versatile you can use any character to put in between, also the Interval between characters can be redesigned, as well as it works with any type of data Alphanumeric, Number or Alphabets. And the best part is that it works with almost all versions of Excel (2007, 2010, 2013 & 2016)