TEXT FUNCTION - LEFT FUNCTION - MID FUNCTION

Question

I have an Excel file that looks like this. In A4 I have put in the number 1023312345, but I have assigned a custom made number format that goes 000000-00000 to be sure that there is a 0 in from of the number - the number should ALWAYS have 6 numbers before dash, then five numbers after dash. So example 030467-12345 when typed into the cell MUST show with the zero in front, and not as 30467-12345 (since Excel usually leaves out 0 in front of a number). 

SO this works ok.

In cell B4 I have used the function LEFT - the problem is that left does NOT pick up the 0 in front, and leaves me without the zero. I have tried to use a text function before the left function to correct this, but it does not work. How can I write the function that picks up the 6 first numbers from cell A4, including the 0 in front, and shows me the result 010233 and not 102331.... See my function in the formula bar on the screenshot.... It picks out the 6 digits, but not the zero.... Any suggestions? I guess it is some sort of TEXT function, but I am unsure about the format to choose. Or is there any other solutions?

Also, finally, in the first personal number - example: 010233-12345 I want to use the MID function to pick out the number 3 (that is in 12345 (last part of the number). I can use MID to count the positions, however, when the format is put in like 000000-00000, and I count 9 positions into the number, Excel starts counting in the number 1023312345 and ends up with 4. I want it to also count the 0 in front and end up with the number 3. How can I make Excel understand that the 0 in front is part of the text and must be included in the function. So - start counting from left from the 0 and 9 positions into the text.

Hope someone understand, and can help??!

Regards from Kathrine

Answer 1

Give this a try...

The left six characters:   =LEFT(TEXT(A4,"000000-00000"),6)

The 3 in 12345:              =MID(TEXT(A4,"000000-00000"),10,1)

The key is to use the TEXT function to create the same value that the custom format produced and then parse that.

Answer 2

Question: Why does the LEN function pick up the 0 in front, and why does not the normal LEFT function pick up the 0 in front?

The LEN function did not pick up the 0 in front, the TEXT function provided it. Here is what is going on in Rory's formula...

Value in A4: 1023312345

The bold part of this formula...

=TEXT(LEFT(A4;LEN(A4)-5);"000000")

grabs all the characters except the rightmost 5 characters... LEN(A4) is the length of all the characters and LEN(A4)-5 is the length of all but 5 of them. The LEFT function makes sure the "all but 5 of them" are the leftmost characters.... those characters are 10233. Then the TEXT function says to present that number as a six-digit number with leading zeroes if there are not enough digits.

The one thing to realize is what you see in the cell is not the value Excel formulas work with... they work with the value you see in the Formula Bar. The TEXT function returns in real text what Cell Formatting displays in the cell. You might also want to take a look at the formulas I posted in my earlier response to your question... you might get a better idea how to parse anything you want from the formatted value... you use the TEXT function to produce a text string equal to what you see in the cell and then use LEFT, MID, etc. on that to pull out whatever characters you want.

Answer 3

Assuming you will always have at least one number present before the hyphen:

=TEXT(LEFT(A4;LEN(A4)-5);"000000")

Answer 4

Hi,

Try it this way.

=LEFT(TEXT(A4,"000000-00000"),FIND("-",TEXT(A4,"000000-00000"))-1)

EDIT....Note my locale uses a comma delimiter so change all the commas to ;