Merging Data From Various Excel Worksheet Cells Without Losing Data

Merging Data From Various Excel Worksheet Cells Without Losing Data

- in Business
41
Comments Off on Merging Data From Various Excel Worksheet Cells Without Losing Data

Merging data from multiple Excel worksheet cells can be very important in a few instances. For instance, you might have imported data from the database like MS Access, SQL or mySql into Excel and you have the name, middle name and surname from the employees in three different cells under their particular headers or labels. You want to create all of the data within the three cells into a single cell having a label like ‘name’. Obviously you can make use of the concatenate function. Or make use of the ‘merge and center’ feature of Excel. However this feature cause loss of data and also the former enables only focusing on one data at any given time.

Utilizing a macro or VBA code it is simple to merge the information from a large number of cells inside a couple of seconds. You utilize a looping process inside your macro. When the data continues to be merged you are able to center or format it based on your needs rapidly and simply with the addition of a couple of more lines of code.

Given here 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

Finish Sub

Remember when you run the macro you can’t undo it.

But assistance is at hands, While using ‘text to columns’ feature underneath the data tab within the ‘data tools’ group you are able to revert to your original data. Obviously, the information within the headers must be joined by hand. For instance, you may want to enter ‘First Name’, ‘iddle Name’ and ‘Last Name’ within the first row so you are aware what sort of data isn’t surprising in every column.

Now let us say you take the above mentioned macro and instantly merge the information inside your worksheet. You might find the information is left centered. It is simple to center the information. Just record a macro by doing the all of the actions that you’d did while formatting data in Excel. Now take a look at the code and you will easily realise why you simply need both of these lines of code to resolve the issue of center alignment:

‘Code to center the written text

Posts(“A:A”).Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

Finish With

Similarly should you desired to ‘autofit’ the information within the cells you’d again record an easy macro to obtain the following type of code:

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

Using VBA or perhaps a macro for such tasks can automate the entire process and also you saw that it is really not hard to learn or implement!

You need not worry if you are not very proficient to use Macros. Comat provides an Excel Macro course that aims at beginners. The modules are developed in accordance to the requirement of the learners. Register yourself at the earliest.

About the author

You may also like

How to Stop Payroll Fraud Dead in Its Tracks

Search your favorite online news source for stories