Forum Home
Press F1
Thread ID: 41743	2004-01-21 00:24:00	XL problem	B.M. (505)	Press F1

Post ID	Timestamp	Content	User
208876	2004-01-21 00:24:00	Let’s say I have the numbers 12345 in cell A1. What I want to do is put the 1 in B1, 2 in C1, 3 in D1, 4 in E1, 5 in F1. Any suggestion anyone?	B.M. (505)
208877	2004-01-21 00:37:00	B.M. Why not use the MID function e.g. B1 =MID(A1,1,1) e.g. C1 =MID(A1,2,1) e.g. D1 =MID(A1,3,1) e.g. E1 =MID(A1,4,1) e.g. F1 =MID(A1,5,1) or similar... Cheers, Babe.	Babe Ruth (416)
208878	2004-01-21 01:22:00	Thanks for that Babe, It did exactly what I wanted until I got down the sheet a bit and found I didn’t have 5 numbers, and in the case of say a single number it needs to go into the 5th position i.e. column F. If there were say two numbers e.g. 12 then the 2 should go in column F and the 1 in column E. Nearly there, but not quite. Interesting anyway because I’ve never previously used the MID function. Cheers Bob	B.M. (505)
208879	2004-01-21 01:46:00	Try using the TEXT function in the formula so that instead of mid(A1,1,1) you would use mid(text(a1,"00000"),1,1) and so on. This should work with any length up to 5 digits. To increase the digits, just increase the "00000" mask portion of the text function. Cheers Miami	Miami Steve (2128)
208880	2004-01-21 01:53:00	You need to add leading '0's to the cells that have less than 5 characters. if your list of numbers is in Col A, run this formula for all rows in Col B (regardless of number of characters, this returns the correct leading 0's. =RIGHT(CONCATENATE("0000",A1),5) Then run the code given to you earlier on Col B	Marlboro (4607)
208881	2004-01-21 02:18:00	B.M. Looks like some others have pretty well given it to you but anyway here was my take on it: B1 =IF(MID(RIGHT(CONCATENATE(" ",A1),5),1,1)<>0,MID(RIGHT(CONCATENATE(" ",A1),5),1,1),"") C1 =IF(MID(RIGHT(CONCATENATE(" ",A1),5),2,1)<>0,MID(RIGHT(CONCATENATE(" ",A1),5),2,1),"") D1 =IF(MID(RIGHT(CONCATENATE(" ",A1),5),3,1)<>0,MID(RIGHT(CONCATENATE(" ",A1),5),3,1),"") E1 =IF(MID(RIGHT(CONCATENATE(" ",A1),5),4,1)<>0,MID(RIGHT(CONCATENATE(" ",A1),5),4,1),"") F1 =IF(MID(RIGHT(CONCATENATE(" ",A1),5),5,1)<>0,MID(RIGHT(CONCATENATE(" ",A1),5),5,1),"") I have used 4 blanks within the concatenate function... HTH Cheers, Babe.	Babe Ruth (416)
208882	2004-01-21 03:40:00	Thanks Guys, Starting with Miami, the Text function didn’t change anything from the original formulae. No idea why. ?:| Marlboro, you make a good point however I can’t add zero’s because a Zero has quite a different meaning to a blank space. Yes I know, just makes things that muck more awkward. :( Babe Ruth, you got to second base. Your formula worked spot on for five and four figures but misplaced less. Now, if two worked why didn’t the other three. I copied and pasted what you posted so I wonder if it’s a typo? The sheet did accept it fine so probably not. All character building stuff aye. :D	B.M. (505)
208883	2004-01-21 03:51:00	Hi B.M. I don't know why that wouldn't work for you. The Text function converts a value to text in a specific format. So if A1 contains the value 430 the formula text(A1,"00000") should return a value of "00430", and applying the mid finction to that should extract each character. Therefore: B1 contains =mid(Text(A1,"00000"),1,1) returns first digit, C1 contains =mid(Text(A1,"00000"),2,1) returns second digit, D1 contains =mid(Text(A1,"00000"),3,1) returns third digit, E1 contains =mid(Text(A1,"00000"),4,1) returns fourth digit, F1 contains =mid(Text(A1,"00000"),5,1) returns fifth digit, Cheers Miami	Miami Steve (2128)
208884	2004-01-21 04:21:00	Your dead right Miami. If I delete the numbers in row A and retype them (they were previously copied and pasted) then things work just as you said. Now, what the hell is going on here is another question. However, the adding of zeros causes further confusion as I mentioned to Marlboro. A zero has a quite different meaning to a blank space in the context of things. Hmmm, the plot thickens.	B.M. (505)
208885	2004-01-21 09:46:00	1. Select the cell or column with the data. 2, Go to Data menu, then Text to Columns 3. Select Fixed Width, then Next. 4. You have the option of specifying where you want the breaks to be, by clicking on the scale on the top. 5. Then Finish. Is this what you want?	rugila (214)
1 2 3