Splitting the content of a cell in a CSV file into separate cells

Question

Hi, I have a large CSV file full of organisations names and addresses. I need to hand over a clean csv file to our developers who need to build a database around them.

The problem is that the street names and numbers are in the same cell, and they need to be separate, so that we can filter organisations by street names.

So, I would like to split the content of the address cell, into two cells. The address cell is built like this: [streetname][space][digits][etc] - So I need to keep only the street name, and move everything after street name to another cell.

An example would be the content cell D7:

[D7]Konkylievej 3, 1. m.f.[/D7]

Which would have to be split like this:

[D7]Konkylievej[/D7]

[E7]3, 1. m.f.[/E7]

The size of the CSV file makes this impossible to do manually. Is there any way to do it automatically?

Answer 1

This changes slightly with the version of Excel you're using, but basically you should select the column, and find "Text to Columns" in the menu (most probably Data Menu in versions before 2007, or Data Ribbon in 2007 onwards.) Then in the dialog box, select "Delimited", then tell excel to use spaces to separate the text to columns.

Hope that works

Answer 2

Since you only need to split this into two cells, you can accomplish this with a short bit of VBA code. Paste this into the Sheet1 code of your VBA workbook (Alt-F11 to get to VBA editor). Change the range from A1:A200 to whatever you need, as well as the different sheet name (your data might not be on Sheet1). Once you've got it set up, hit F5 and it should do what you're looking for. Obviously you might want to test this in a copy of your original data to make sure you get the expected results. I populated several cells with "123 Main" and afterward had "123" in column A and "Main" in column B. It will take everything to the right of the first space and put it into col B, and trim everything to the right of the space and leave what's left in column A.

Option Explicit

Sub SeparateAtTheSpace()
    Dim cell As Range, areaToSplit As Range
    Set areaToSplit = Sheet1.Range("A1:A200")
    For Each cell In areaToSplit
        If IsEmpty(cell) = False Then
            cell.Offset(0, 1).Value = Right(cell.Value, InStr(1, cell.Value, " ") + 1)
            cell.Value = Left(cell.Value, InStr(1, cell.Value, " "))
        End If
    Next cell
End Sub

Answer 3

Hi Kasper

Not the most elegant solution but please try this...

Option Explicit

Sub SeparateAtTheSpace()
        Dim cell As Range, areaToSplit As Range
        Dim str As String
        Dim i As Integer
        Set areaToSplit = Sheet1.Range("D1:D305")
        For Each cell In areaToSplit
                If IsEmpty(cell) = False Then
                        str = cell.Value
                        For i = 1 To Len(str)
                                If IsNumeric(Mid(str, i, 1)) Then
                                        If IsEmpty(cell.Offset(0, 1)) = True Then
                                                cell.Offset(0, 1).Value = Right(str, Len(str) - i + 1)
                                                cell.Value = Left(cell.Value, i - 1)
                                        End If
                                End If
                        Next i
                End If
        Next cell
End Sub