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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,473
Office Version
  1. 365
Platform
  1. Windows
4ndy

Welcome to the Mr Excel board!

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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,136,926
Messages
5,678,614
Members
419,776
Latest member
mikelowski

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
Top