# How do I insert a (-) into a cell with text

#### 4ndy

##### New Member
I have a spreadsheet that contains 30,000 lines of part numbers with just numerals and letters.

I need to end up with the following:

1234567-ABCDE-1234

from

1234567ABCDE1234

Can anyone help me, your help would be very much apprecaited.

Andy...

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
If the data is consistent, then try:

=left(A1,7)&"-"&mid(a1,8,5)&"-"&right(a1,4)

where A1 is the first data to change...then copy down

Fantastic, very much appreciated.

Cheers

Andy...

4ndy

Welcome to the Mr Excel board!

Here's another option:
=REPLACE(REPLACE(A1,13,0,"-"),8,0,"-")

Hi,

I see it's solved, but this might be useful if the strings are not consistent.
(just did the work, why not post it )
Code:
``````Option Explicit

Sub split_alpha_numeric()
'Erik Van Geit
'060921
'ABCD123EF12GH
'TO GET
'ABCD-123-EF-12-GH

Dim rng As Range
Dim arr As Variant
Dim LR As Long          'Last Row
Dim i As Long
Dim ch As Integer
Dim temp As String

'**** EDIT ****
Const sep As String = "-"   'separator
Const FR As Integer = 1     'First Row
'**** END EDIT ****

LR = Cells(Rows.Count, 1).End(xlUp).Row
If FR = LR Then MsgBox "only one item in list ?", 32, "EXIT": Exit Sub

Set rng = Range("A" & FR & ":A" & LR)
arr = rng

For i = 1 To LR
temp = vbNullString
If Len(arr(i, 1)) <> 1 Then
For ch = Len(arr(i, 1)) To 2 Step -1
If IsNumeric(Mid(arr(i, 1), ch, 1)) = IsNumeric(Mid(arr(i, 1), ch - 1, 1)) Then
temp = Mid(arr(i, 1), ch, 1) & temp
Else
temp = sep & Mid(arr(i, 1), ch, 1) & temp
End If
Next ch
End If
arr(i, 1) = Left(arr(i, 1), 1) & temp
Next i

'delete ".Offset(0, 1)" to overwrite the data
rng.Offset(0, 1) = arr

End Sub``````
kind regards,
Erik

Replies
4
Views
313
Replies
1
Views
268
Replies
4
Views
210
Replies
1
Views
274
Replies
3
Views
211

1,219,918
Messages
6,150,956
Members
450,996
Latest member
darko1515s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back