Picking two columns of data and combining

I would like to pickup two columns of numbers (text formatted), combining together and place in another column.

Example: (The columns go from row 9 through 49)

Column M has 7 numbers with blank cells in between numbers, and Column U has 11 numbers also with blank cells in between numbers.

I want to combine both columns (total of 18 numbers) in Column AA with no blanks.

I know this has probably been answered some where on the forum but I cannot find it.

Any suggestions?

tRY:

=SUBSTITUTE(M1&U1," ","")

Where M1 and U1 are first numbers to combine

If you want the result to be an actual number you can use in formulas.. then try

=SUBSTITUTE(M1&U1," ","")+0

@NBVC, I think the issue here is one of no interspersed blanks.

It's a shame the numbers are stored as text else you could use a basic LARGE configuration ... you still can but you'd need to use via an Array to coerce
[assumes numbers can be returned in numerical order rather than the order in which they appear]

(obviously given XL2008 you have no VBA support)

I misread the question... I didn't realize it was blank cells.. I thought the cells themselves had numbers with some blank spaces..between.... I guess my coffee hasn't kicked in this morning

In the sample...try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

This lists column A numbers first, then column B numbers below with no blanks...not sure if that is acceptable or not...

The array works beautifully on the sample.

When I went to my "real" file and the columns in contention are Column M9:M59 and Column U9:U59 the array works in Column AG9:AG59 except there's a large number of rows (approximately 15 rows) with blanks then numbers.

What appears to be happening is the formula is picking up Column M rows of blanks then Column U.

Please Login or Register  to view this content.

Or is it this array needs to start at Row 1?

This part: ROWS($M$8:$M8) both times should be $M$1:M$1 at the cell you first enter the formula in: Then confirm with CSE keys and copy down

Also check that the blank cells are truly blank and that someone didn't enter spaces with the spacebar...

I just tried your sample, by placing the items in column A at M9 and the items in B at U9 and ran the formula:

Please Login or Register  to view this content.

CSE confirmed and copied down.

There are alot of blanks in the bottom part of column M, but those don't show up in the formula column.. it goes smoothly from last number in M to first number in U.

If you want attach the actual workbook deleting all columns except M and U....and I could verify.

The only way I get those gaps is if I enter a space in some cells in column M...

Try going to the cell after the last number in column M and just press delete. Then copy that blank cell down to M59.. now is the result still the same?

Ok, you have formulas that produce nulls... therefore use:

Please Login or Register  to view this content.

This part:

COUNTIF($H$9:$H$59,"?*") changes and accounts for counting items in a range excluding formula blanks.