200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello,
I am an excel novice and am trying to combine every possible 2 separate 2 digit numbers but when I try to combine numbers like 01 with any other 2 digit number the number 1 is doubled instead of reflecting 01.
I was able to customize the cell to reflect a 2 digit result but the 0 is not picked up by the excel formula.
Can someone please help me solve this problem. I have attached the the file I am working on.
Any help would be greatly appreciated. Thank you
Looking at your file, it seems you enter a base number in F1 (currently 72) and generate some varaibles (in B5:E5) from digits in B1:E1 then attempt to combine the possible combinations of those in cells B13:X16.
I think the problem is that the cell B5 for example appears to contain 01 (because the custom cell format "00" always shows it as two digits of text) but in fact the cell contains a sum, currently the number 1 (not 01) since you add 0, 0, 1 and 0. (If you remove your custom formatting of B5 and make it General instead say, you'll see the 1.)
Therefore when you do the string functions on B5 like in cell A13:
=LEFT(F1,1)&MID(F1,2,1)&LEFT(B5,1)&RIGHT(B5,1)
then LEFT(B5,1) returns 1 since B5 contains just 1 (despite appearances) and RIGHT(B5,1) also returns 1 (so you get a four-digit number ending ..11 rather than 01).
To fix that, in the revised file attached I've changed the contents (not just the appearance) of B5 (now in yellow) to a two character string using the Text function on your sum, changing the cell formula to the following:
=TEXT(B1+B2+B3+B4,"00")
This means B5 now contains the string "01" (not the number 1) so LEFT(B5,1) returns the "0" from the string and RIGHT(B5,1) returns the "1" (rather than 11 before). Now cell A13 (previously 7211) shows 7201, which is what you are seeking.
I've done the same for cells C5:E5 but I haven't changed any of the formulae in cells B13:X16.
Hope this fixes your file.
