removing a space "from earlier questions"

[Guest]

Let me ask again,
I 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?
I have tried what was suggested by by others.
1
If it is just a trailing space you can use edit>replace find what put a
space and leave replace with
blank. If they are numbers copied from the web you can click in the find
what box, then hold
down alt while typing 0160 on the numpad, then release alt, leave replace
with blank. Finally if this is done on a regular basis see:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

and also
2You 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)

niether work so let me give you an idea
a1= 3434"space" meaning (3434 ) i need to convert (3434 ) to (3434), I have over 5,000 records to do this to without writing code and just using the formula bar.

 

[RagDyer]

You should stay in your original thread.
Then, all you would have to say in your post, was that *nothing* worked,
instead of starting this new thread and repeating all the suggestions that
you say didn't work.

You could try this:

Select your data and then:
<Data> <TextToColumns> <Next> <Next> <Finish>
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Let me ask again,
I 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?
I have tried what was suggested by by others.
1
If it is just a trailing space you can use edit>replace find what put a
space and leave replace with
blank. If they are numbers copied from the web you can click in the find
what box, then hold
down alt while typing 0160 on the numpad, then release alt, leave replace
with blank. Finally if this is done on a regular basis see:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

and also
2You 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)

niether work so let me give you an idea
a1= 3434"space" meaning (3434 ) i need to convert (3434 ) to (3434), I have
over 5,000 records to do this to without writing code and just using the
formula bar.

 

[Kevin H. Stecyk]

Hi,

1) Your tone sounds aggressive. I hope I am simply misreading your message.
We are helping because we want to, not because we have to.
2) Please continue everything in one thread. It helps us keep track of what
is going on.
3) What didn't work when you tried the other approaches.
4) If you want, send me your workbook (e-mail address removed) and I will attempt
to fix your problem and tell you how I did it.

Best regards,
Kevin

Let me ask again,
I 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?

I have tried what was suggested by by others.
1
If it is just a trailing space you can use edit>replace find what put a
space and leave replace with
blank. If they are numbers copied from the web you can click in the find
what box, then hold
down alt while typing 0160 on the numpad, then release alt, leave replace
with blank. Finally if this is done on a regular basis see:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

and also
2You 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)

niether work so let me give you an idea
a1= 3434"space" meaning (3434 ) i need to convert (3434 ) to (3434), I

have over 5,000 records to do this to without writing code and just using
the formula bar.

 

[Peo Sjoblom]

When you say it doesn't work, what happens? If indeed you have a trailing
space any of these suggestion would work.
Also use the same thread when reposting, now you have posted the same
question in 3 different threads.
What happens if you use

=--TRIM(SUBSTITUTE(A1,CHAR(160),""))

try that in an adjacent cell and copy down, make sure the help column is
formatted as general or numbers


--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Let me ask again,
I 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?

I have tried what was suggested by by others.
1
If it is just a trailing space you can use edit>replace find what put a
space and leave replace with
blank. If they are numbers copied from the web you can click in the find
what box, then hold
down alt while typing 0160 on the numpad, then release alt, leave replace
with blank. Finally if this is done on a regular basis see:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

and also
2You 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)

niether work so let me give you an idea
a1= 3434"space" meaning (3434 ) i need to convert (3434 ) to (3434), I

have over 5,000 records to do this to without writing code and just using
the formula bar.

 

[David McRitchie]

Hi "imbackd" <[email protected]>,
The TRIMALL macro will do exactly what you are asking.
You should read the topics above and below the macro as
they explain how you can find what you actually have if it
is not a space and is is not Char(160). The other thing is
that you have to make a selection of the cells that you want
to trim before running the macro.

When the cells are trimmed with TRIMALL they are essentially
reentered, and General will accept digits as numbers and
the are numbers, it will accept letters as text and those are
text. See the comments about testing for ISNUMBER and
for ISTEXT.

Let me ask again,
I 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?

I have tried what was suggested by by others.
1
If it is just a trailing space you can use edit>replace find what put a
space and leave replace with
blank. If they are numbers copied from the web you can click in the find
what box, then hold
down alt while typing 0160 on the numpad, then release alt, leave replace
with blank. Finally if this is done on a regular basis see:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

and also
2You 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)

niether work so let me give you an idea
a1= 3434"space" meaning (3434 ) i need to convert (3434 ) to (3434), I have over 5,000 records to do this to without writing code

and just using the formula bar.