View Full Version : XL problem

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?

Babe Ruth

21-01-2004, 12:37 PM

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.

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

Miami Steve

21-01-2004, 01:46 PM

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

Marlboro

21-01-2004, 01:53 PM

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

Babe Ruth

21-01-2004, 02:18 PM

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.

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

Miami Steve

21-01-2004, 03:51 PM

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

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.

rugila

21-01-2004, 09:46 PM

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

21-01-2004, 11:51 PM

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

22-01-2004, 12:00 AM

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).

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

parry

22-01-2004, 05:49 AM

Im curious, why do you want to seperate each number into a different cell?

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.

parry

22-01-2004, 10:38 AM

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.

odyssey

22-01-2004, 12:55 PM

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

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.

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

Babe Ruth

22-01-2004, 02:06 PM

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.

parry

22-01-2004, 03:27 PM

LOL I see :-)

You should be able to be the ultimate in lazy by using web queries but its quite hard to do off that site - when I tried anyway.

Probably just as well, my form has been pants over the last couple of days. :-)

Fire-and-Ice

22-01-2004, 04:21 PM

> Home Run Babe! :D

> It’s the TAB’s safe and the numbers are the

> form line for the horses.

So Babe is in for a share of the winnings due to arrive shortly then? ;-) :D

You bet Fire-and-Ice, they that help me, I help.

Not a problem!!

As an aside, now that the initial problem has been dispensed with are any of you guys interested in neural engines?

I probably should make this a separate post, but, then again, why not start here?

Cheers

Bob

Oh ****, missed your post Parry, glad you had a laugh!

Believe it or not, the information, which should be provided in any shape or form, is seen as being secret by some people.

I mean, if I can match a computer spreadsheet against them then so what? They just reduce the dividend and I go into the situation knowing I can never break them. However, there is a dollar to be made, by using science and ignoring sentiment in these situations.

Would you like the winner of the Wellington Cup??????? :D

Cheers

Bob

Powered by vBulletin® Version 4.2.5 Copyright © 2018 vBulletin Solutions Inc. All rights reserved.