Excel Excel Problem

[stuartroyal]

I'm sure this question is fairly simple but I'm new to the more advanced features of excel.. I want to remove spaces from in between numbers like this 8001 00 456 7897 and make them look like this 8001004567897. I did this using the =SUBSTITUTE(A1," ","") formula I then used autofill to create a new column in B, however when I try to save the newly formed column on a new sheet it comes up as REF"". How do I keep it in the correct format? What is the best way to accomplish this? Please explain as simple as possible. Thank you for your time

 

[spanna]

Do you want the cells still to be active or is this just a once only copy & paste operation

If its a once only then do Paste Special and select "values"

If you need it to be active and continue to update then you need to add the sheet name to the cell name in the formula

 

[spanna]

Just had to check about how to add sheet name

just type sheet name followed by ! followed by cell name

So if data is on Sheet1 in cell A12 then to refer to that cell you need to type Sheet1!A12

 

[stuartroyal]

Thankyou that proved helpful. I still have a few clean up problems I have formatted the cells to add +44 in front of the number however in a lot of the cells there is a small green triangle in the top left corner of each cell and until I click on each cell individually the +44 does not appear in the cell. How do I get all the cells to appear correct ie. +44123456789 without clicking on each individual cell? If you or someone can help it would be much appreciated

 

[spanna]

How did you add the +44

I would have done a custom format

"+44"0

that doesn't give me the green triangle indicator

 

[stuartroyal]

Green Triangle

Originally used +44########## tried "+44"0 but still got the green triangle. I also have some numbers formatted as so (01779)474816 and would like to remove the brackets is this possible? Thanks for your patience!

 

[spanna]

Try this

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"(",""),")","")," ",""),"-","")

change A4 for whatever cell you want

(I had to google that)

 

[stuartroyal]

You know your onions! Well done that worked a treat. I am still having problems with the green triangle in top left corner I have worked out how to correct the error? and have got rid of the green triangle however the numbers are still on the left of each cell as i.e. 07899123456 and until I click on each cell they do not convert to the right side of the cell in the right format i.e. +447899123456. Any ideas?

 

[spanna]

Are you applying the custom format to the cells with the SUBSTITUTE command?

What version of Excel are you using - I'm using 2007

Can you send me an extract of what you are trying to do - PM me for email address

 

[stuartroyal]

Spanna can't send PM at this stage my account won't allow it. Contact details can be found atwww.premierracingclub.com
Regards
Stuart

 

[spanna]

sent an email to info@ on that site

will that get to you?

 

[Anon_F]

Spanna can't send PM at this stage my account won't allow it. Contact details can be found atwww.premierracingclub.com
Regards
Stuart

!!SPAM ALERT!!

 

[spanna]

No spam yet

though I did use a temp e-mail address