Removing rows with spaces at beginning of cell?

[shawn]

It seems someone has been inserting a random amount of spaces after the city
in our accounting software. Not all of them have it, but a good number do.
It annoys me like crazy, so I figured I'd fix them all.

I've exported just the name of the company and city into Excel. There's over
2500+.

Col A is Company Name. Col B is City. I've taken Col B and reversed the text
(I have ASAP Utilities installed if that helps), this way I can see which
ones have spaces at the end.

Now how can I tell Excel to delete all the rows which do NOT contain spaces
in the beginning of the cells in Col B? This way I'll only be left with the
ones with spaces, so I can easily pull them up in our accounting software
and fix them.

 

[Dave Peterson]

I'd add an extra column and fill it with formulas like:

=(left(b1,1)=" ")

Then I could filter to show just the False's and delete those visible rows.

 

[Gord Dibben]

Have you tried the TRIM function?

You may be able to get rid of the extraneous spaces without deleting any
rows.

In a helper column enter =TRIM(cellref)

Copy down.


Gord Dibben MS Excel MVP

 

[shawn]

Sounds like a good idea. Will try it out now. Thanks.

 

[shawn]

That won't work. I don't want to get rid of the spaces in Excel. I just want
to show the entries who have spaces so I can go back into my accounting
software and correct them.

 

[shawn]

What I have used is

=(left(B1)=" ")

then copied that all the way down. It will allow me to filter all the FALSE
and remove them.

The thing is with that it will report true on anything that has a space in
it. I only need it to look at the beginning of the cell and see if there is
a space or not. That looks in the whole cell.

Eitherway, it helps me a lot and I thank you.

 

[Dave Peterson]

I think you did something wrong.

maybe you have calculation set for manual????

Or maybe you didn't put the right formula in the right row????

 

[shawn]

Won't let me sort now, though. I can only sort smallest to largest or
largest to smallest. Can't sort A-Z like normally. If I sort smallest to
largest or the other, it will have the false and true's all over the place.

What I have used is

=(left(B1)=" ")

then copied that all the way down. It will allow me to filter all the
FALSE and remove them.

The thing is with that it will report true on anything that has a space in
it. I only need it to look at the beginning of the cell and see if there
is a space or not. That looks in the whole cell.

Eitherway, it helps me a lot and I thank you.

 

[shawn]

Nevermind.. got it to sort.. seems to do exactly what I need, thx.

What I have used is

=(left(B1)=" ")

then copied that all the way down. It will allow me to filter all the
FALSE and remove them.

The thing is with that it will report true on anything that has a space in
it. I only need it to look at the beginning of the cell and see if there
is a space or not. That looks in the whole cell.

Eitherway, it helps me a lot and I thank you.