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 |

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 copypaste specialvalues either in-place or elsewhere
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Gary" wrote in message
...
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 |

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



"Gary" wrote:

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 |