I want to create a number series with a prefix but I cannot us the sheet>fill>series with an increment of anything as it returns a INVALID value error due to the prefix
eg what I want is
BNQ.001
BNQ.002
etc etc
so can I put BNq in one column and then create a number series in the adjacent column then merge both columns?
How someone can help
thanks Ian
I am not sure where you get the Invalid value error. I can fill series no problem starting with BNQ.001 and Sheet > Fill Cells > Fill Series, it also works just selecting the first cell and using the bottom right corner, drag filling down.
Maybe it is because you still need to use the numbers? In which case formatting the cells as "BNQ."000 will allow the numbers to be used and will give you the leading zeroes. Right-click on a cell, select Format Cells, under the tab Numbers select User-defined and enter "BNQ."000 in the Format cells field. Enter 1 in the cell and you will get BNQ.001 but you can use that number in calculations
The question you actually asked is how to merge data from 2 cells. The word you need to remember is concatenate as that is what you are trying to do. Enter BNQ. in cell A1, '001 in cell B1, in cell C1 enter =A1&B1 (you could instead enter =CONCATENATE(A1;B1)). Note that you would need to drag fill down as Sheet > Fill Cells > Fill series seems to revert the B column cells to General number format. Cheers, Al
I am not sure where you get the Invalid value error
probably due to not using Series Type [o] AutoFill