far Posted November 12, 2007 Share Posted November 12, 2007 Guys I have a row in an excel spreadsheet and I need all the numbers here to start with 0 (zero) is there a quick formula or way I can add a zero to all the ones which dont have a zero (screen dump attached) cheers FAR Quote Link to comment Share on other sites More sharing options...
far Posted November 12, 2007 Author Share Posted November 12, 2007 also is there anyway to merge two columns into one? Quote Link to comment Share on other sites More sharing options...
MRX Posted November 12, 2007 Share Posted November 12, 2007 Something like: =IF(LEFT(B17,1)="0",B17,"0"&B17) Quote Link to comment Share on other sites More sharing options...
Ady Posted November 12, 2007 Share Posted November 12, 2007 hummmmmmmmmmm havent a clue Quote Link to comment Share on other sites More sharing options...
far Posted November 12, 2007 Author Share Posted November 12, 2007 I havent got a clue how to put a formula in by the way lol! how do I do that? Quote Link to comment Share on other sites More sharing options...
MRX Posted November 12, 2007 Share Posted November 12, 2007 To merge two columns is easy just use the '&' function example =a1&b1 if you need a space between them =a1&" "&b1. Quote Link to comment Share on other sites More sharing options...
MRX Posted November 12, 2007 Share Posted November 12, 2007 How many rows of data you got? Quote Link to comment Share on other sites More sharing options...
far Posted November 12, 2007 Author Share Posted November 12, 2007 How many rows of data you got? 3975 Quote Link to comment Share on other sites More sharing options...
danhicks22 Posted November 12, 2007 Share Posted November 12, 2007 Excel makes the contents of a cell into a formula whenever it starts with '='. Copy and paste the formula given earlier into a cell to use it. Quote Link to comment Share on other sites More sharing options...
danhicks22 Posted November 12, 2007 Share Posted November 12, 2007 Once you've got your cell references right for one formula, copy and paste the resulting cell value (the cell you put the formula into, now containing the results of the formula calculation) to the rest of the column and it will update the references for you so it's appropriate to the row. Quote Link to comment Share on other sites More sharing options...
MRX Posted November 12, 2007 Share Posted November 12, 2007 And change the cell reference (B17 used in previous formula) to the cell that has the number in on your sheet. Quote Link to comment Share on other sites More sharing options...
AndyT Posted November 12, 2007 Share Posted November 12, 2007 Need a 0. Right click on a cell, format, custom and you'll get a list with the 0's in it. Highlight it. Should be ok. Enter formulae. Highlight cell and type formula into the long box at the top of the sheet. Click the tick or hit return. Formula entered. I am no expert. Quote Link to comment Share on other sites More sharing options...
far Posted November 12, 2007 Author Share Posted November 12, 2007 Guys I havent got a clue what the hell is going on! I am a complete numpty when it comes to computer stuff! I have followed your instructions but it wont work. Can someone just put something up step by step that a five year old could follw? Quote Link to comment Share on other sites More sharing options...
far Posted November 12, 2007 Author Share Posted November 12, 2007 ffs! It still doesnt work, Can someone please add Zeros to the attached (first column) and make the second and 3rd column into one column! cheers Quote Link to comment Share on other sites More sharing options...
far Posted November 12, 2007 Author Share Posted November 12, 2007 it wont let me attach it, its too big Quote Link to comment Share on other sites More sharing options...
AndyT Posted November 12, 2007 Share Posted November 12, 2007 Oops sorry fella. To get the 0, right click, format, custom and set the box to text. It will then read as it's typed without dropping the 0. You need that set to every cell. Only way I know is to move the cursor to the bottom right corner of the formatted cell, the cursor will change to a little + left click and hold as you drag down to the last cell you need to use, or the whole sheet, and let go. This will have copied the format to every cell. Quote Link to comment Share on other sites More sharing options...
danhicks22 Posted November 12, 2007 Share Posted November 12, 2007 Have a look at this. First column is the list of values without leading '0's; the second column contains the formulae for adding the '0' - note that the cell displays the calculated value until you click on it, then it displays the formula in the bar near the top; the third contains the formula for concatenating two cells; the fourth is the same but with a space between the values. Quote Link to comment Share on other sites More sharing options...
far Posted November 12, 2007 Author Share Posted November 12, 2007 Have a look at this. First column is the list of values without leading '0's; the second column contains the formulae for adding the '0' - note that the cell displays the calculated value until you click on it, then it displays the formula in the bar near the top; the third contains the formula for concatenating two cells; the fourth is the same but with a space between the values. ok I think I am getting somewhere but how do I copy & paste the formula without it just pasting into the box? i.e. where does the feckin formula go? Quote Link to comment Share on other sites More sharing options...
far Posted November 12, 2007 Author Share Posted November 12, 2007 ..so if I want the whole column to work on that particular formula do I highlight the whole column then what? paste the formula somewhere? Quote Link to comment Share on other sites More sharing options...
MrRalphMan Posted November 12, 2007 Share Posted November 12, 2007 Just to jump on the band wagon.. I've updated the attached SS to do it as per your list.. No (09) then add (09). Quote Link to comment Share on other sites More sharing options...
AndyT Posted November 12, 2007 Share Posted November 12, 2007 Nice. Dan. Hope you don't mind me saving that too. I think far is only using telephone numbers so no need for calculation. Thought the above would be easiest for him. Don't know about merging two columns though. What bit in your example does that? Quote Link to comment Share on other sites More sharing options...
danhicks22 Posted November 12, 2007 Share Posted November 12, 2007 Select the cell you want to copy by single clicking on it (don't double click as this will change the focus to be on editing the cell contents, not the sheet as a whole), then select the 'edit' menu and choose 'copy' (or hold down CTRL and press 'c' - that's the shortcut), then click and drag over the cells you want to paste it into - they will become highlighted - then select 'edit' and 'paste' (CTRL + V). Hope that makes sense. Excel likes to make you think it's clever by having a different display value (the calculated value) to the one it actually contains (the formula). Quote Link to comment Share on other sites More sharing options...
MrRalphMan Posted November 12, 2007 Share Posted November 12, 2007 ok I think I am getting somewhere but how do I copy & paste the formula without it just pasting into the box? i.e. where does the feckin formula go? This needs to go into the function bar at the top.. Little X and tick to the left of it. ..so if I want the whole column to work on that particular formula do I highlight the whole column then what? paste the formula somewhere? Once one cell works. Click on it and you'll see a little black box on the bottom right of the cell. You can either click and drag it down the list or if it's unbroken then double click on it. Quote Link to comment Share on other sites More sharing options...
danhicks22 Posted November 12, 2007 Share Posted November 12, 2007 Nice. Dan. Hope you don't mind me saving that too. I think far is only using telephone numbers so no need for calculation. Thought the above would be easiest for him. Don't know about merging two columns though. What bit in your example does that? No probs Columns C and D do it. You can use '&' as an in-line operator or use the 'concatenate' function with a comma separated list of values/cell references within the brackets. Quote Link to comment Share on other sites More sharing options...
far Posted November 12, 2007 Author Share Posted November 12, 2007 thanks guys - I gave up in the end and got a techie to do it! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.