Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
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. |
#3
![]() |
|||
|
|||
![]()
"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. |
#4
![]() |
|||
|
|||
![]()
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. |
#5
![]() |
|||
|
|||
![]()
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. |
#6
![]() |
|||
|
|||
![]()
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. |
#7
![]() |
|||
|
|||
![]()
"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. |
#8
![]() |
|||
|
|||
![]()
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. |
#9
![]() |
|||
|
|||
![]()
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. |
#10
![]() |
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel | |||
Excel - turn a page number in a header from horizontal to vertical | Charts and Charting in Excel | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel | |||
updating invoice number on excel spreadsheet | Excel Worksheet Functions | |||
Can the number of times undo is used in Excel 2002 be increased? | Setting up and Configuration of Excel |