Removing "space" after imported numbers

[barrett@thebrooksgroup.co]

I import lists into Excel. Before formatting phone or fax numbers, I remove these characters ()- and "spaces" in the numbers. However, I sometimes find a "space" after the number. When I run a Find/Replace, where I find the "space" and then replace it with nothing, the "space" still remains and I cannot format it. Are you aware of this situation and what can I do to remove the "space" AFTER the numbers? BTW, this also occurs with Zip Codes as well.

 

[Aladin Akyurek]

On 2002-04-22 08:18, barrett@thebrooksgroup.com wrote:
I import lists into Excel. Before formatting phone or fax numbers, I remove these characters ()- and "spaces" in the numbers. However, I sometimes find a "space" after the number. When I run a Find/Replace, where I find the "space" and then replace it with nothing, the "space" still remains and I cannot format it. Are you aware of this situation and what can I do to remove the "space" AFTER the numbers? BTW, this also occurs with Zip Codes as well.

Try:

=CODE(RIGHT(A1))

where A1 houses a suspect entry.

This will give you a number n, probably, 160.

Use

=SUBSTITUTE(A1,CHAR(n),"")+0

+0 converts the entry to number after the cleaning.
This message was edited by Aladin Akyurek on 2002-04-22 08:26

 

[Mark W.]

Are you sure that it's a space? What does =CODE(RIGHT(A1)) return where A1 is a cell containing one of your values?

 

[AJ]

One solution would be to download the excellent asap-utilities from http://asap-utilities.com
This has many handy formatting features, one of which is to "Delete leading trailing and excessive spaces"

 

[barrett@thebrooksgroup.co]

On 2002-04-22 08:23, Mark W. wrote:
Are you sure that it's a space? What does =CODE(RIGHT(A1)) return where A1 is a cell containing one of your values?

The value is 160 - now what do I do?

 

[Aladin Akyurek]

On 2002-04-22 08:39, barrett@thebrooksgroup.com wrote:

On 2002-04-22 08:23, Mark W. wrote:
Are you sure that it's a space? What does =CODE(RIGHT(A1)) return where A1 is a cell containing one of your values?

I feel I'm repeating myself:

=SUBSTITUTE(A1,CHAR(160),"")+0

Copy down this as far as needed.

The value is 160 - now what do I do?

 

[Mark W.]

On 2002-04-22 08:39, barrett@thebrooksgroup.com wrote:

On 2002-04-22 08:23, Mark W. wrote:
Are you sure that it's a space? What does =CODE(RIGHT(A1)) return where A1 is a cell containing one of your values?

The value is 160 - now what do I do?

In an unused or inserted column enter the formula, =LEFT(A1,LEN(A1)-1)+0. Copy down as far as needed. Copy/Paste Special... Values replaceing your original values with these new ones.

 

[barrett@thebrooksgroup.co]

On 2002-04-22 08:46, Aladin Akyurek wrote:

On 2002-04-22 08:39, barrett@thebrooksgroup.com wrote:

On 2002-04-22 08:23, Mark W. wrote:
Are you sure that it's a space? What does =CODE(RIGHT(A1)) return where A1 is a cell containing one of your values?

I feel I'm repeating myself:

=SUBSTITUTE(A1,CHAR(160),"")+0

Copy down this as far as needed.

The value is 160 - now what do I do?

Sorry about your repeating yourself. I got it to work. Thank you very much for your help.