Removing a space

[Guest]

I asked a question earlier
need to remove an extra space in a cell (ex. cell a1 is 5543 and a space) it is set as general not a number, I have over 5700 rows, doing it manually is impossible can i do it? in a formula
However it did not work and I Do not understand typing visual basic code that well, I do unterstand formulas
Plesae help??

 

[Kevin H. Stecyk]

Hi,

You might try something like this:
=VALUE(LEFT(A1,LEN(A1)-1))

If you don't care about value (number vs text) you could shorten the
equation to
=LEFT(A2,LEN(A2)-1)

Good luck

Best regards,
Kevin

I asked a question earlier
need to remove an extra space in a cell (ex. cell a1 is 5543 and a space)

it is set as general not a number, I have over 5700 rows, doing it manually
is impossible can i do it? in a formula?

However it did not work and I Do not understand typing visual basic code

that well, I do unterstand formulas.

 

[David McRitchie]

This must be one of those many scattered postings of yours
that someone mentioned. .

You will not get much out of newsgroups if you do not stick to
one thread, nor will you get much if you don't use your name
(first and last) when post..

You also have spent more time saying the macro doesn't work
you don't want to learn macros, and you don't want to type then
you would have spent learning how to use a macro with the
information already supplied.

It appears that rather than doesn't work that
you did not install the macro, so how can you honestly say it
does not work if you didn't install it..

You say it's too much to type.
You don't have to type much of anything you copy and paste.

Once you've installed the macro it would take you less than
1/2 minute from start to finish to change 2000 cells the next time..

You cannot use simply Ctrl+H (replace) to remove the space or
char(160) they have to be reentered afterwards and that is what the
macro will do for you all at once. All of the other solutions
which you did not really try would take a whole lot longer
than using a macro and there is no cleanup involved with
getting rid of the extra column if you used a macro instead.

Also formulas that simply strip the rightmost character could
put your data at risk if some don't actually have an extra space.
You'd be much safer with a formula like
=TRIM(SUBSTITUTE(D2,CHAR(160)," "))
but then after using the fill-handle to copy down or the
name box and Ctrl+D to copy down you are only 1/3
of the toward completion there is a lot more to do afterwards.
You are much better off with a macro.

So to repeat, instructions to install and use a macro
http://www.mvps.org/dmcritchie/excel/getstarted.htm
the TRIMALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
use of the fill-handle (if you don't use the macro)
http://www.mvps.org/dmcritchie/excel/fillhand.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I asked a question earlier
need to remove an extra space in a cell (ex. cell a1 is 5543 and a space) it is set as general not a number, I have over 5700

rows, doing it manually is impossible can i do it? in a formula?

However it did not work and I Do not understand typing visual basic code that well, I do unterstand [understand] formulas.
Plesae [Please] help??