Condensing one column to another without spaces.

G

Gary

I have a column that has data in it mixed with spaces that are uneeded. I
want to create another column next to it with the same data in it but without
the spaces.

ie

A B
1 a | a
2 | d
3 d | c
4 |
5 c |
 
M

Max

Try a quick non-array approach ?

Assuming source data is in col A, from row1 down

Put in C1: =IF(A1<>"",ROW(),"")

Put in B1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(C:C,ROWS($A$1
:A1)),C:C,0)))

Select B1:C1. fill down till the last row with data in col A

Col B will return the desired results

Freeze col B with a copy>paste special>values either in-place or elsewhere
 
B

bj

To keep things in the same order will take two columns
if your data is in column A
copy your column of data to to column B
in C1 enter
=if(b1<>"",row(),"")
and copy down to the bottom of your data
select columns B and C and sort by column C
If you then delete column C You will have column B have the same data as
Column A but with no blank spaces
 
Top