Avatar of zachvaldez
zachvaldez
Flag for United States of America asked on

remove space

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
Microsoft Excel

Avatar of undefined
Last Comment
zachvaldez

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

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

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

ASKER
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?
Patrick Matthews

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
zachvaldez

ASKER
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
Patrick Matthews

Did you put the formula in N50?  That would cause a circular reference :)
SOLUTION
telyni19

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
zachvaldez

ASKER
sounds good to do sort of backup column. I'll try..
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
zachvaldez

ASKER
If Id like to trnsform back  (999)999-9999 to display as (999) 999-9999 (with a space), what is the formula?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Patrick Matthews

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

ASKER
I just want to see both sides of the formula to understand fully
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy