adding cells

[junlee]

Please me in adding cell with alphabets. I attach a pic of what i want it to look like. Is there a command to do it for you instead of me outting one by one myself? Ex. 'A' must first followed by 'B' and so on, futhermore, if there a CApital 'A' and a lower case 'a' the capital 'A' goes first.

[oldchippy]

Hi junlee,

Try this in B2, then formula fill across and down. There may be one or two cells that may be wrong i.e. "dD" instead of "Dd", but see how it goes?

=LEFT($A2,1)&LEFT(B$1,1)&MID($A2,2,1)&MID(B$1,2,1)&MID($A2,3,1)&MID(B$1,3,1)&MID($A2,4,1)&MID(B$1,4,1)

[junlee]

I tried it and it work for B2, however it doesn't work for the other cells. By the way the pic i attach was only a small portion of it. What i really want to do concist of 6 letters. 'ABCDEF'. Sry if i wasn't being clear.

[oldchippy]

OK, I've adjusted it for six characters. So put this in B2 and place your curser over the little black square, your curser will change to a black cross, click and drag across, and down - see attached

=LEFT($A2,1)&LEFT(B$1,1)&MID($A2,2,1)&MID(B$1,2,1)&MID($A2,3,1)&MID(B$1,3,1)&MID($A2,4,1)&MID(B$1,4,1)&MID($A2,5,1)&MID(B$1,5,1)&MID($A2,6,1)&MID(B$1,6,1)

[junlee]

ty alot, but is there anyway fF will be arrange like Ff?

[oldchippy]

Not with the formula, but away round it would be to select all the cells with formulas in then Copy > Paste Special > Values.
Then go to Edit > Find type in fF then click Replace tab and type "Ff" > Replace All. Repeat the Find/Replace proceedure for any others.

[junlee]

Wow ty so much u saved so muhc of my time.
Ty alot.

[oldchippy]

Glad to help - thanks for the feedback

[junlee]

Er i check it but the replacment thing didn work correctly. It replace everything. For example it is suppose to replace aA to Aa. It did but it even replace AA to Aa, so it mess everything up. CAn u fix this problem .
Ty in advance.

[oldchippy]

Hi junlee,

That shouldn't happen, you must be doing something wrong. If you do it the way I said it should work perfectly Can someone else confirm

[junlee]

i dunno if this will effect it but my version for excel in 2007.

[oldchippy]

I've got 2003, but I wouldn't have throught so

[junlee]

Did it work for u?

[oldchippy]

Every time without any problems?

[junlee]

Weird i did everything u said still doesnt work. I tihknk its becasue i have 2007.

[oldchippy]

According to Microsoft, it's the same from Excel 2002 onwards (includes 2007)

http://support.microsoft.com/kb/288291

[Bryan Hessey]

Er i check it but the replacment thing didn work correctly. It replace everything. For example it is suppose to replace aA to Aa. It did but it even replace AA to Aa, so it mess everything up. CAn u fix this problem .
Ty in advance.

Hi,

for the Replace, did you see the replace Options and set to Match Case?

---

[junlee]

that was my problem. Sry im kind of new in excel. Ty for all your help and sry to bother you.

[Bryan Hessey]

that was my problem. Sry im kind of new in excel. Ty for all your help and sry to bother you.

don't be sorry, we were all new to Excel once.

oldchippy will be glad to see this resolved, so thanks for your response.

---

[junlee]

Np. One last thing and i wont bother you guys anytime soon. Can u make me a formula for ABCDEFG. This is one last one. Ty alot. You guys saved me so much time.

[oldchippy]

Hi junlee,

Glad you've got it sorted and thanks to Bryan to. Here's the one for seven letters

=LEFT($A3,1)&LEFT(B$1,1)&MID($A3,2,1)&MID(B$1,2,1)&MID($A3,3,1)&MID(B$1,3,1)&MID($A3,4,1)&MID(B$1,4,1)&MID($A3,5,1)&MID(B$1,5,1)&MID($A3,6,1)&MID(B$1,6,1)&MID($A3,7,1)&MID(B$1,7,1)

Just add the bit on the end

[junlee]

Hi again. Im having another issue with the new formula.

[junlee]

It always get screwed up at the last row Im not sure why.

[Bryan Hessey]

It always get screwed up at the last row Im not sure why.

your formula is a row out, the =LEFT($A3,1)&LEFT(B$1,1)&MID~~ should be on row 3

---

[junlee]

what do u mean by row 3 ?

[Bryan Hessey]

what do u mean by row 3 ?

columns are lettered A B C D E etc

Rows are numbered 1 2 3 4 5 6 etc

see Row 3 (aka the third row)

---

[junlee]

And i found another error, all the combination are wrong. I think the formula is wrong. It adds up wrong. Tkae a look at my previous pic.

[Bryan Hessey]

And i found another error, all the combination are wrong. I think the formula is wrong. It adds up wrong. Tkae a look at my previous pic.

your previous pic is wrong because you put a Row 3 formula into Row 2, and then filled it downwards.

---

[junlee]

OK i got it right now but what about row 2? I need a formula for it also

[junlee]

Nvm i solved it. Ty alot for help

[Bryan Hessey]

Nvm i solved it. Ty alot for help

the easy way was to put the formula, which was for row 3, into Row 3, and copy from there and paste to Row 2, then formula fill downwards.

---