can excel change number 1532 to 1235 running order

B

Bluesy69

I need help in excel,formatting of numbers. Example 1548,6981,1032,6710 to
display in excel in order preferences as 1458,1689,0123,0167. Thank you.
 
A

Alan

Bluesy69 said:
I need help in excel,formatting of numbers. Example
1548,6981,1032,6710 to display in excel in order preferences as
1458,1689,0123,0167. Thank you.
Click to expand...

Hi,

I almost got to a totally generic solution for you, but I ran out of
time.

This assumes that each number is four digits long, but only the last
part {1000;100;10;1} makes that assumption and I imagine that would be
easy to fix to generalise totally.

Anyway, try this:

{=MMULT(TRANSPOSE(SMALL(VALUE(MID(TEXT(A1,"@"),ROW(INDIRECT("1:"&LEN(T
EXT(A1,"@")))),1)),ROW(INDIRECT("1:"&LEN(TEXT(A1,"@")))))),{1000;100;1
0;1})}

Enter as an array formula.

HTH,

Alan.
 
B

Bluesy69

Thanks Alan,
Thank you for the effort. I wil copy and paste the format you given.
 
K

Kassie

Hi Alan,

Your formula stops at the first occurence of ("1:"&LEN(TEXT(A1,"@")))),
indicating that there is an error in the formula. Maybe you should recheck
it?
 
A

Alan

Kassie said:
Hi Alan,

Your formula stops at the first occurence of
("1:"&LEN(TEXT(A1,"@")))), indicating that there is an error in the
formula. Maybe you should recheck it?
Click to expand...

Hi Kassie,

It looks okay to me.

Perhaps it has a problem with a specific input in A1?

If you highlight a section of the formula in the formula bar such as
("1:"&LEN(TEXT(A1,"@"))) it should evaluate just that section of the
formula allowing you to debug for whatever is causing it to error.

That particular section shoudl evaluate to "1:4" as an example (if you
put a four digit number in A1).

I am not at a PC with excel right now, so I cannot try it, but I have
to admit I only tested it on 4 digit numbers so there could be a
problem with longer or shorter ones.

HTH,

Alan.
 
K

Kassie

I tried it with 1325 in Column A, but as I said, when array entering, it
comes up with an error, and highlights the TEXT portion. I don't know this
formula at all, so I abide by your superior knowledge. Since I copied and
pasted, I don't see how I could have made an error on my side. Maybe just
check it on Excel when you get a chance
 
P

Peo Sjoblom

You can use this

=TEXT(MMULT(TRANSPOSE(SMALL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))),{1000;100;10;1}),REPT(0,LEN(A1)))

it needs to be in text format otherwise a number like 100 will be returned
as 1, however if you always have 4 digit numbers you can use

=MMULT(TRANSPOSE(SMALL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))),{1000;100;10;1})

then a custom format like 0000
 
B

Bluesy69

Thank you Peo Sjoblom.

Peo Sjoblom said:
You can use this

=TEXT(MMULT(TRANSPOSE(SMALL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))),{1000;100;10;1}),REPT(0,LEN(A1)))

it needs to be in text format otherwise a number like 100 will be returned
as 1, however if you always have 4 digit numbers you can use

=MMULT(TRANSPOSE(SMALL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))),{1000;100;10;1})

then a custom format like 0000
Click to expand...
 
Top