• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Slitting an excel spread sheet into multiple columns

I have an excel spread sheet that has rows with everything in the same column like this.

1S2373JTU990L8MA
1S2373PU799WYGCW
1S23731WU99AH1F0

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?

Thanks

0
Wildone63
Asked:
Wildone63
  • 3
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
First column:

=MID(A1,3,4)&"-"&MID(A1,7,3)

Second column:

=MID(A1,10,99)

Kevin
0
 
sdwalkerCommented:
If it has to be in a macro, using zorvek's formulae, it would look like this.
sdwalker

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

0
 
zorvek (Kevin Jones)ConsultantCommented:
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

Kevin
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
zorvek (Kevin Jones)ConsultantCommented:
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.

Kevin
0
 
sdwalkerCommented:
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,
sdwalker
0
 
Wildone63Author Commented:
Thank You very much. This worked our great for me. I truly appreciate it.

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now