Excel Excel Problem

Status
Not open for further replies.
S

stuartroyal

Joined
Apr 28, 2010
Messages
5
Reaction score
0
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
 
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
 
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
 
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
 
How did you add the +44

I would have done a custom format

"+44"0

that doesn't give me the green triangle indicator
 
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!
 
Try this

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

change A4 for whatever cell you want

(I had to google that)
 
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?
 
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
 
Status
Not open for further replies.
Back
Top