remove space

zachvaldez
zachvaldez used Ask the Experts™
on
My column for phone field display the phone list with extra space ....
(999) 999-9999.
I'd like to remove the space after the")" so to show as

(999)999-9999 .What is the column formula to edit the list?
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
This will remove all spaces:

=SUBSTITUTE(A2," ","")

This is a little more specific, replacing a single space after the parenthesis:

=SUBSTITUTE(A2,") ",")")
Top Expert 2010

Commented:
If you might have multiple spaces after the parenthesis, consider:

=SUBSTITUTE(TRIM(A2),") ",")")

Author

Commented:
thanks,
 while going down the list, I saw phone formats as "999-999-9999"
How will I transform it to "(999)999-9999" format so it is the same all throughout?
Top Expert 2010

Commented:
zachvaldez,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

The sample file should clearly illustrate both the input and the expected output given that input.  The sample file need not be very large, but it should have enough examples to cover the expected range of values/scenarios.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick

Author

Commented:
Im getting circular reference error when I do teh substitute function.
I point it to the cell  and pasted the formula with the cell number(substitute "A2" with "N50") and I get the error
Top Expert 2010

Commented:
Did you put the formula in N50?  That would cause a circular reference :)
In other words, you need to have a data column and a transformation column, where you put the SUBSTITUTE formula or whatever else you need to do to your data. Once your data appears as you please, you can replace your original data column with the transformed data. That will avoid circular references. In fact, if you try to make a formula work on its own cell, you've already overwritten whatever was previously in the cell.

Author

Commented:
sounds good to do sort of backup column. I'll try..

Author

Commented:
If Id like to trnsform back  (999)999-9999 to display as (999) 999-9999 (with a space), what is the formula?
=LEFT(A1,5) & " " & RIGHT(A1,LEN(A1) - 5)
This would work as long as all of them start with the area code in parentheses as the first five characters.
Top Expert 2010

Commented:
This seems to be meandering a bit.  I think the original question has been answered :)

Author

Commented:
I just want to see both sides of the formula to understand fully

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial