Issue with Concatenate and keeping some custom formatting

[DDR]

Hi All,

I have solved a previous issue per: http://www.excelforum.com/excel-prog...87-spaces.html

The issue I now have is the formatting of the cells that I am concatenating is essentially wiped out when I concatenate the results.

For example, Cell1 has a custom format of 00, Cell2 is just text, Cell3 is a custom format of ddmmyy, Cell4 is a custom format of hhmmss, Cell5 and Cell6 are just text.

How do I go about ensuring that the concatenate function places the text as an exact join of the data contained in the source cells?

For the record, the formula (courtesy of watersev) is:

Please Login or Register  to view this content.

[Andrew-R]

Try changing this line:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

[DDR]

Hi Andrew-R,

Thanks for your input.

This simple change works for all except the first cell that gets concatenated.

The first cell (A1 in this case) has a value as displayed of 01, and is custom formatted as 00. When it gets concatenated, it displays as 1. My wish is for it be 01 as displayed in the original cell.

How might this be achieved please?

[Andrew-R]

So far as I can see your code isn't including A1 in the concatenation.

Your range loop runs through column A, but then you are concatenating based on a column offset of 1-6 (the loop 'i'), so the first cell concatenated will be B1.

[DDR]

Hi Andrew-R,

For this particular Concatenation there is only one row being concatenated.

What I am doing is getting the Data from a different sheet that has the data I want only in one row. In this case my data is in A1, B1, C1, D1 E1, F1 and G1. Previous to the code we are discussing is some code that copies these cells from the other sheet into the sheet I will concatenate from. Once the data is existing in the new sheet, the Macro code (as shown) then runs. There is only data contained in Row 1 for this particular block of code and it does run.

So when I have A1 = "01", B1 = "ABCDEFG ", C1 = "010112", D1 = "010407", E1 = "TEST TEXT ", F1 = "[287 spaces]"

The concatenated value that is placed in the adjacent cell H1 = "1ABCDEFG 010112010407TEST TEXT [287 spaces]"

What I am after however is for H1 = "01ABCDEFG 010112010407TEST TEXT [287 spaces]"

I hope this makes sense.

Many thanks for your assistance.

[Andrew-R]

When I test concatenating using the .Text property on a cell formatted to "00" it works fine and "01" is placed that the front of the string.

In the code you're using are you starting off with something like:

Please Login or Register  to view this content.

If so then you'll need to specify the text property in that line as well...

Please Login or Register  to view this content.

[DDR]

The code I have reads:

Please Login or Register  to view this content.

If I place a .Text at the end of "A1", like so:

Please Login or Register  to view this content.

I end up with a syntax error, so I'm obviously doing something wrong.

[Andrew-R]

Sorry, just me being stupid. I didn't spot this line:

Please Login or Register  to view this content.

Which needs to be ...

Please Login or Register  to view this content.

[DDR]

That got my 01 to appear correctly thank you, however it's broken the ddmmyy and hhmmss formatted C1 and D1 (respectively) cells that have been concatenated.

What I now get is H1 = "01ABCDEFG 01/01/20120.044525462962963TEST TEXT [287 spaces]"

[Andrew-R]

So the code you're now using is:

Please Login or Register  to view this content.

That seems to work fine for me. Can you post a sample workbook with a line of data in?

[DDR]

Andrew-R, thanks so much.

I really appreciate your help on this one.

This last lot of code works perfectly.

I had:

Please Login or Register  to view this content.

Instead of:

Please Login or Register  to view this content.

Thanks again for your help.