CODEWITHSUNDEEP
excelexcel-formulavba
Anjali
Anjali

Reputation: 2698

splitting the data in Excel 2013

I am very new to Excel macros. I have below data in Excel:

Col1 Col2       col3    col4  Col5
32    000-001                   1
32    002-001                   100
32    005-000                   20

and so on. I have 6313 rows in my excel spreadsheet.

I want to split col2 and put the first three numbers before hyphen in col3 and last three numbers in col4. I also want to remove hyphen from col2 so my final output will be like this:

col1   col2     col3    col4    col5
32    000001    000      001    1
32    002001    002      001    100
332   005000    005      000    20

This is what I already tried. I tried to replace Hyphen with empty space and then I lost all the leading zeros. I don't want to lose all the leading zero from col2.

Any help will be greatly appreciated.

Upvotes: 0

Views: 71

Answers (4)

pnuts
pnuts

Reputation: 59475

Assuming Col1 is in A1: Move ColumnB to ColumnC, split Text to Columns with - as delimiter (choose Text for both columns) then in B2 and copied down to suit:

=C2&D2

Upvotes: 2

urdearboy
urdearboy

Reputation: 14580

If doing this outside of VBA is an option (tagged Excel-Formula), you could make use of the following three functions: Left, Right, and Substitute.

You will need to add an extra column to implement this.

enter image description here

Which will yield the following results:

enter image description here

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

Sub aljali()
    Dim i As Long, N As Long

    Range("A:E").NumberFormat = "@"
    N = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To N
        Cells(i, "B").Value = Replace(Cells(i, "B").Value, "-", "")
        Cells(i, "C").Value = Left(Cells(i, "B").Value, 3)
        Cells(i, "D").Value = Right(Cells(i, "B").Value, 3)
    Next i
End Sub

NOTE:

The key to this is setting the format of the cells to Text. This facilitates the preservation of those leading zeros.

Upvotes: 2

user4039065
user4039065

Reputation:

Try,

With Worksheets("sheet8")
    For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
        With .Cells(i, "B")
            .Resize(1, 3).NumberFormat = "@"
            .Resize(1, 3) = Array(Replace(.Value2, Chr(45), vbNullString), _
                           Split(.Value2, Chr(45))(0), _
                           Split(.Value2, Chr(45))(1))
        End With
    Next i
End With

Upvotes: 1

Related Questions