Excel - get left 8 digits including preceeding zero 1

[srogers]

I have a list of numbers in varying length(less than 9 digits). What I want is to add preceeding zeros to make each number 9 digits long. Then I want to take only the left 8 digits and sort the numbers by the last two digits.

I can add preceeding zeros with a custom format but then when I use the LEFT function it ignores the zeros.

Is this something that can be done with formulas or will I need to use VBA?

Thank you

 

[dyarwood]

To get the left function working you could use something like

Left("A1",len("A1")-2)

You could then pad this out with zeros to get the 8 digits required.

 

[TonyJollans]

Hi srogers,

You can, as far as I know, only sort on actual cell values, so you must get the two digits you want into a column by themselves. It is not necessary to have leading zeroes to sort (unless you want strings and have a custom sort order), so to get a column like that ..

Assuming your numbers are in column A, put in B1 ..

[blue][tt] =MOD(INT(A1/10),100)[/tt][/blue]

.. and copy it down. Then sort on column B.

Do your custom format on the original numbers (and hide or delete the column B as you wish)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[macropod]

Hi,

To pad the number in A1 with leading zeros, so that you have a 9-digit string, use:
=TEXT(A1,"000000000")
Note that this is stored as text, not as a number.

To get the nominal 7th & 8th digits from the same source value for sorting, use:
=MID(TEXT(A1,"000000000"),7,2)
or:
=VALUE(MID(TEXT(A1,"000000000"),7,2))
if you need to hold these as a number instead of as text.

Alternatively, if the first formula is in B1, you could use:
=MID(B1,7,2)
or
=VALUE(B1,7,2))

Cheers

 

[xlbo]

the Format won't actually add the zeros - it just displays them - that's why the LEFT function won't work

You need to use both Tony AND Macropods solutions to get the result you have requested

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[srogers]

I used macropods solution to separate the numbers into 4 columns of 2 numbers each and then sorted them by the last column, then the next to last column and so on. They really don't have to be in 4 columns (1 would have been fine) but I wasn't sure how else I would get them sorted in that order.

Thank you for your help everyone.

 

[macropod]

Hi srogers,

To get the 9-digit string in A1 padded with leading zeros, you really only need the first of the formulae that I posted and, to extract the 7th & 8th digits from that string, the second formula would have sufficed.

Cheers