Hi,, I have a col that is the result of an if function.
=IF(H105="a",B105,"")
Which results in a col of names in col O if there is an "a" in col H.
But if it is not an "a" it is a space, how can I do it so that all the spaces are removed
and I end up with a col of names with no spaces please.
Len
Hi Len,
The problem is that Excel does not provide the ability to enter a “Null” character as result of a formula. The “” you have in your formula is not actually a space but a special Excel “blank”, which screws up every calculation involving it!
So to overcome that you can use this formula:
=IF(H105="a",B105, NA())
This will create a #N/A, instead of the special Excel “blank”, in all cells that do not meet the if criteria.
Then go to “Find & Select” > “Go To Special” and click on “Formulas”. Deselect everything but “Errors”. Once all #N/A (i.e. Errors) are selected you can press delete to delete them and be left with empty cells.
I am very sorry if I did not make it clear but my formula is ok but I am ending up with a column of name with spaces and I want to know how to collapse them with a column that has no spaces, EG.
Fred
Charlie
Harry
Tom
Alan
To
Fred
Charlie
Harry
Tom
Alan
Hope that explains it better
Len
The only way I can think of, is to use filtering on your columns and filter out the blank cells.
Thankyou Dimitri,,I can do it that way .
Happy now
Len
Quote
