Slitting an excel spread sheet into multiple columns

Posted on 2010-01-11
Medium Priority
Last Modified: 2012-05-08
I have an excel spread sheet that has rows with everything in the same column like this.


I need to write a script that will split it out...
The 1st and 2nd characters need to be deleted.. then the first seven are the part number and the remaing characters are the serial number

So the first line should be 2373-JTU  990L8MA
the second line should be 2373-PU7 99WYGCW
the third line should be 2373-1WU 99AH1F0

Can anyone help me write a script or macro that would split this column out for me?


Question by:Wildone63
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26284985
First column:


Second column:


LVL 12

Expert Comment

ID: 26285063
If it has to be in a macro, using zorvek's formulae, it would look like this.

Sub SplitData

numRows = cells(rows.count, 1).end(xlup).row

for i = 1 to numRows
  range("B" & i).value = mid(cells(i,1), 3, 4) & "-" & mid(cells(i,1), 7,3)
  range("C" & i).value = mid(cells(i,1), 10, 99) 
next i

End Sub

Open in new window

LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26285164
If I were doing a macro I would do it in place:

Public Sub SplitSerialNumbers()

    Dim Cell As Range

    For Each Cell In [A1].Resize(Application.CountA([A:A]))
        Cell.Offset(0, 1) = Mid(Cell, 10, 99)
        Cell = Mid(Cell, 3, 4) & "-" & Mid(Cell, 7, 3)
    Next Cell

End Sub

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 26285170
To add VBA code to a worksheet or chart code module in an Excel workbook, right-click on the worksheet or chart tab at the bottom of the window and select View Code. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook. To find a worksheet or chart module when already in the VBE, press CTRL+R to open the VBE project explorer. Find the module in which the code will be placed - each worksheet and chart module is pre-assigned a name such as "Sheet1 (Sheet1)" where the name inside the parenthesis is the tab name. Double-click the desired module and paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.

LVL 12

Expert Comment

ID: 26285384
Wildone - as the only Savant in the Excel zone, Kevin is a crazy good coder.  If you have no empty rows in your dataset, his will work great.
Good luck,

Author Closing Comment

ID: 31675571
Thank You very much. This worked our great for me. I truly appreciate it.


Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question