[ad_1]
Merging Data From Different Excel Worksheet Cells Without Losing Data

Merging data from multiple Excel worksheet cells can be quite important in certain situations. For example, you may have imported data from a database like MS Access, SQL or mySql into Excel and you now have the first name, middle name and last name of the employees in three different cells under their respective headers or labels. You wish to bring all the data in the three cells into one single cell with a label like ‘name’. Of course you can use the concatenate function. Or you can use the ‘merge and center’ feature of Excel. But this feature cause data loss and the former allows only working on one data at a time.

Using a macro or VBA code you can easily merge the data from thousands of cells within a few seconds. You use a looping process in your macro. Once the data has been merged you can center or format it according to your requirements quickly and easily by adding a few more lines of code.

Given below is the macro code to merge data in Excel cells:

Sub MergeAndCenter()

Dim myText As String, mySpace As String

mySpace = ” “

Cells(1, 1) = “Name”

Cells(1, 2) = “”

x = 2

Do While Cells(x, 1) <> “”

Cells(x, 1) = Cells(x, 1) & mySpace & Cells(x, 2)

Cells(x, 2) = “”

x = x + 1

Loop

End Sub

Remember once you run the macro you cannot undo it.

But help is at hand, Using the ‘text to columns’ feature under the data tab in the ‘data tools’ group you can revert back to your original data. Of course, the data in the headers will have to be entered manually. For example, you may need to enter ‘First Name’, ‘iddle Name’ and ‘Last Name’ in the first row so that you know what kind of data is to be expected in each column.

Now let’s say you run the above macro and automatically merge the data in your worksheet. You may find that the data is left centered. You can easily center the data. Just record a macro by doing the all the actions that you would have done while formatting data in Excel. Now have a look at the code and you’ll easily understand why you just need these two lines of code to solve the problem of center alignment:

‘Code to center the text

Columns(“A:A”).Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

End With

Similarly if you wanted to ‘autofit’ the data in the cells you would again record a simple macro to get the following line of code:

Columns(“A:A”).EntireColumn.AutoFit

Using VBA or a macro for such tasks can automate the complete process and you saw that it’s not at all difficult to learn or implement!

[ad_2]

Source by Dinesh Takyar