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.

Not possible

--
Regards,

Peo Sjoblom


"Bluesy69" wrote in message
...
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.

"Bluesy69" wrote in message
...

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.


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.

Thank you Peo for the prompt answer.

"Peo Sjoblom" wrote:

Not possible

--
Regards,

Peo Sjoblom


"Bluesy69" wrote in message
...
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.

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

"Alan" wrote:

"Bluesy69" wrote in message
...

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.


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.

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?

"Alan" wrote:

"Bluesy69" wrote in message
...

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.


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.

"Kassie" wrote in message
...

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?


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.

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

"Alan" wrote:

"Kassie" wrote in message
...

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?


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.

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

--
Regards,

Peo Sjoblom


"Kassie" wrote in message
...
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

"Alan" wrote:

"Kassie" wrote in message
...

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?


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.

Thank you Peo Sjoblom.

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


"Kassie" wrote in message
...
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

"Alan" wrote:

"Kassie" wrote in message
...

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?


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.