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

4ndy

New Member
Joined
Jul 23, 2004
Messages
3
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
4ndy

Welcome to the Mr Excel board!

Here's another option:
=REPLACE(REPLACE(A1,13,0,"-"),8,0,"-")
 
Upvote 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
'START WITH
'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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top