VBA Express : Excel - Concatenate using first space before address as delimiter

Excel

Concatenate using first space before address as delimiter

Ease of Use

Easy

Version tested with

2000

Submitted by:

lucas

Description:

This will concactate a name and address in Cell A1 into Just the name in Cell A1 and the remainder of the address into Cell A2

Discussion:

You have a list of names and addresses on one line and you wish to separate the name from the address. Put them in column A at the top of the sheet and run this macro and they will be seperated.

Code:

instructions for use

			Put this code In a standard module: 
 
Option Explicit 
Sub concactate() 
    Dim i               As Long 
    Dim j               As Long 
    Dim k               As Long 
    Dim n               As Long 
    Dim LastRow         As Long 
     'select all used range in column A
    LastRow = Range("A65536").End(xlUp).Row 
    For i = 1 To LastRow 
        k = 0 
        n = 0 
        For j = 0 To 9 
            k = InStr(2, Range("A" & i).Text, j) 
            If k > 0 Then 
                If n = 0 Then 
                    n = k 
                Else 
                    n = Application.WorksheetFunction.Min(n, k) 
                End If 
            End If 
        Next j 
        If n > 0 Then 
            Range("B" & i).Value = _ 
            Mid(Range("A" & i).Text, n, Len(Range("A" & i).Text)) 
             
            Range("A" & i).Value = _ 
            Left(Range("A" & i).Text, n - 2) 
        End If 
    Next i 
     
End Sub

How to use:

Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
On the toolbar of the Visual Basic Editor, go to insert - module
In the module pane paste the code above.
Close the Visual Basic Editor by clicking the X in the upper right corner or go to File-Close

Test the code:

On the main menu go to tools-macro-macros.
In the dialog window select concactate and then click run.
Your data in column A will be concactated

Sample File:

concactate.zip 6.85KB

Approved by mdmackillop

This entry has been viewed 157 times.