Concatenate cells and maintain original formatting

Question

We are in the process of migrating from an Excel based system to a database driven system and need to upload a significant amount of data into the database. I created a workbook that will take the data stored in our master workbook and split it into worksheets that can be uploaded directly into the database tables. Most of the project went well, but there is one problem. I need to concatenate two cells and maintain the original formatting.

Specifically, I need to concatenate the cells for a Site # and a Device ID

I Started with something basic like:

=CONCATENATE(C2,"-",D2)

That is where I encountered my first problem. Some of the site numbers leading zeros that are part of the formatting from source data and I need the leading zeros.

That prompted my next attempt:

=CONCATENATE(TEXT(C2,"000000000"),"-",D2)

This kept the leading zeros but also, as expected, added leading zeros to the site #'s that should not have leading zeros. There are legacy sites that have not yet been converted to the new site # format.

My next effort also worked about as well as I expected. I tried checking the length of the cell value to only add the zeros in the concatenated cell if they were only present in the source cell. As I expected, the leading zeros were not includes in the length.

Is there a way this can be done using Excel functions? If not, please provide some pointers (not asking for complete code, just breadcrumbs. I don't learn as much when someone else writes the code as I do when I struggle with it) to create a custom function to concatenate the cells.

Answer 1

Try this quick user defined function.

In a standard module code sheet,

function concSiteDevice(s as range, d as range, _
                        optional delim as string = "-")

    concSiteDevice = join(array(s.text, d.text), delim)

end function

This utilizes the value as it looks in the cell, not the raw value.

On the worksheet as,

 =concSiteDevice(C2, D2)

Wih the optional delim argument, you can change the hyphen to any other character(s). For instance, to use a @ symbol,

 =concSiteDevice(C2, D2, "@")