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
208886	2004-01-21 11:51:00	Maybe a couple of addenda to my suggestion above wouldn't go amiss. (a) That above suggestion, if your numbers are not all of equal length, may leave your empty cells to the right rather than to the left of the filled ones, which you may not want.. (b) You can get around this by adding (say) 10,000,000 to each number and then deleting the 1 in the leftmost column, but this still gives you some zeros which you don't want. (c) A FULL SOLUTION is as follows, so long as you don't have too many digits to exceed Excel's IF function (I think about 7) but even if this is the case you shouldn't have any trouble in finding a workaround. Number to have its digits spread out over columns is in A1. Into A2 paste the function: =IF(LEN(A1)=1," "&A1,IF(LEN(A1)=2," "&A1,IF(LEN(A1)=3," "&A1,IF(LEN(A1)=4," "&A1,IF(LEN(A1)=5," "&A1,IF(LEN(A1)=6," "&A1,A1)))))) Copy this from A2 and paste special - values into A3. Then use the Text to Columns approach, from A3, that I outlined above and you should get a result exactly as you specified and without surplus zeros. But I note on previewing this that PressF1 doesn't seem to reproduce the correct number of spaces in the above IF function. The correct rendition is that (between the quotation marks) you need 6 spaces after the Len(A1)=1, 5 spaces after the Len(A1)=2, 4 after Len(A1)=3, etc. rather than what seems to be only a single space that the PressF1 preview records in each case. (Why does it do this?????? ) You'll probably have to add the correct spacing yourself as appropriate into the pasted function.	rugila (214)
208887	2004-01-21 12:00:00	Alternatively, if you need to count the spaces, or save yourself from putting them in, try =IF(LEN(A2)=1,"@@@@@@"&A2,IF(LEN(A2)=2,"@@@@@"&A2,IF(LEN(A2)=3,"@@@@"&A2,IF(LEN(A2)=4,"@@@"&A2,IF(LEN(A2)=5,"@@"&A2,IF(LEN(A2)=6,"@"&A2,A2)))))) and when done use Excel's Replace facility to delete the @'s (or replace them with whatever you want).	rugila (214)
208888	2004-01-21 14:09:00	Thanks rugila, I'll give that a go. I couldn't sleep pondering the problem so I've spent the last couple of hours playing with Babe's proposition without success. It oh so nearly works. Think I'll leave it until later this morning and go and get some shut eye. Cheers Bob	B.M. (505)
208889	2004-01-21 17:49:00	Im curious, why do you want to seperate each number into a different cell?	parry (27)
208890	2004-01-21 22:10:00	Right, up and around again all bright eyed a bushy tailed . (I wish) First to rugila: The data – text to columns is what I want but it sets the numbers up left justified regardless of where they are in the cell and consequently they are in the wrong place unless they are 5 figured . I can’t see how to adjust this phenomenon . Soooo I tried your little formula, (making sure I corrected the number of spaces) and that was so close . It left lots of 1,2,3 & 4 figures one short with one space to the right . Well I thought, lets whack another space in the various parts of the formula so we have 7 spaces after Len(A1)=1, 6 after Len(a1)=2 etc etc and lo and behold everything in the right place . Looks like we have found a way although I don’t understand why . Thanks for all your trouble through the wee small hours rugila . Next Babe Ruth: Your effort was so close to what was required and I wonder if the reason it didn’t work perfectly was the website flogging a few spaces in your formula as it did with rugila . Possibly you may like to check just in case . (Can’t have a 100% pass ruined by a space flogging website can we?) Now Parry: If I were to tell you what this is all about I’d have to kill you! :( Probably wouldn’t have to as you’d die laughing! :D Have a guess for a bit of fun .	B.M. (505)
208891	2004-01-21 22:38:00	Dunno what your doing, safe cracking? Whatever it is I bet you are making it a lot more complicated than it needs to be. :-) Nobody can tell you an alternative way of doing something unless they know what your goal is so secrecy has a price. ;-) Theres a big difference between formatting and the actual data, so if your wanting spaces before or after data then it may be better to have actual spaces inluded depending upon what your doing. If its purely for display then formatting is the way to go.	parry (27)
208892	2004-01-22 00:55:00	Looks like you've solved the problem anyway, but I thought I'd post an alternative solution for future reference as it seemed like an interesting problem. So assuming your value is A1 of any length (and may or may not include spaces at either end) then in the following cells you would have: B1 = IF(LEN(VALUE(A1))>=5,LEFT(RIGHT(VALUE(A1),5),1),"") C1 = IF(LEN(VALUE(A1))>=4,LEFT(RIGHT(VALUE(A1),4),1),"") D1 = IF(LEN(VALUE(A1))>=3,LEFT(RIGHT(VALUE(A1),3),1),"") E1 = IF(LEN(VALUE(A1))>=2,LEFT(RIGHT(VALUE(A1),2),1),"") F1 = IF(LEN(VALUE(A1))>=1,RIGHT(VALUE(A1),1),"") The use of the value function is to strip any extra spacing that may have gotten into the cell (either before or after the number). Cheers Dave	odyssey (4613)
208893	2004-01-22 01:15:00	Home Run Babe! :D Once I put the spaces back between the quotation marks, (that I think the website ungraciously removed) everything worked perfect. Would be nice if someone could fix this little peculiarity with the Website. Anyway, thanks to everyone for their input. I like the safecracking Parry. You’re darn near right. It’s the TAB’s safe and the numbers are the form line for the horses. If you’re familiar with formguides or racing you’ll know what I mean. The problem occurred because the Website I download a lot of the information from has been upgraded and whilst the designers know all about Website design they no nought about racing and form presentation so we’ve gone from a site working great to one that’s very pretty but doesn’t function as it should. I believe it’s called progress these days. ;\ So that’s it, thanks again everyone.	B.M. (505)
208894	2004-01-22 01:45:00	Odyssey: Last post crossed somewhere . That worked good Dave . It is slightly different to Babe’s in that if a letter pops in, as they sometimes do, it returns #value! which can be a bonus at times as it lets you know if your trying to process the alphabet . Just glad so many came to the rescue as it would have been a very very long time before I cracked it . Cheers Bob	B.M. (505)
208895	2004-01-22 02:06:00	That's great B.M. Yes this website-forum does tend to remove multiple spaces within replies... but I did say > I have used 4 blanks within the concatenate function... Anyways glad to help out.. got some extra pointers out of this also. :-) Cheers, Babe.	Babe Ruth (416)
1 2 3