Removing "space" after imported numbers

Joined
Apr 21, 2002
Messages
3
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
Are you sure that it's a space? What does =CODE(RIGHT(A1)) return where A1 is a cell containing one of your values?
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top