Problem getting correct values when copying Concatenate formula

Question

I have done this a thousand times, but yesterday I started having trouble with the Concatenate formula.  I have a spreadsheet with 16000 rows that have names formatted thus:  xxx,xxx.  I need to add a space between the comma and the first name to return a value of:  xxx, xxx.

I have successfully parsed the name into two fields by using the Text to Columns function.  Now I want to put the fields back together using =concatenate(text1,","," ",text2).  I get the correct results on the first row where I created the formula.  But, when I copy the formula down the remaining rows, I get the same value that is returned in the first row, not the values for the fields in the subsequent rows. 

I have tried using various Paste Special options, but get the same results. 

The only thing that I have found that will change the results in subsequent rows is to click on the fx, then click Enter*.* That makes the formula return the correct value.  But, I can't see myself going through that process 16000 times!

Is this a new bug?  I have used the Concatenate function many times before and have never run across this particular problem.

Answer 1

Hi,

Could you post your complete CONCATENATE formula next to your 1st row of data?