|
display a number in one cell as text in more than one cell?
Hi,
I need to be able to display a number eg. $18735.67 across mulitple cells as text. I need the numbers to read as follows: ONE EIGHT SEVEN THREE FIVE 67 where the spaces between each number denote separate columns and the cents are displayed as numerical. Any help would be much appreciated. Storm |
display a number in one cell as text in more than one cell?
make a lookup table as below 1 one 2 two 3 three etc etc assume number is 1234.56 and is in cell A1 B1= text(A1,"0.00") ie it still LOOKS like 1234.56 C1 =left(B1) ie 1 D1 = mid(A1,2,1) ie 2 and so on You may need to check the length of the text string =len(B1) and where the decimal is =search(B1,".") to make it universal You can either have all these formulas in separate columns or combine them for tidyness. -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=529517 |
display a number in one cell as text in more than one cell?
Hi Robert,
Thanks for your answer but I am not that up there with creating formula with that many variables.... But you have given me an idea that I will investigate further. IF...THEN. Just need to tackle length's and positions Janelle "robert111" wrote: make a lookup table as below 1 one 2 two 3 three etc etc assume number is 1234.56 and is in cell A1 B1= text(A1,"0.00") ie it still LOOKS like 1234.56 C1 =left(B1) ie 1 D1 = mid(A1,2,1) ie 2 and so on You may need to check the length of the text string =len(B1) and where the decimal is =search(B1,".") to make it universal You can either have all these formulas in separate columns or combine them for tidyness. -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=529517 |
display a number in one cell as text in more than one cell?
in cell A1 is your amount for a 7 digit dollar amount, in cell B1 put =TEXT(A1,"0000000.99") in cells C1 to C10 put the words Zero One Two ~~ Eight Nine in the first amount-word cell put =OFFSET($C1,MID($B1,COLUMN()-3,1),0,1) and formula drag this to the right. in the .cents cell put =TEXT(MOD(B1,1),".00") this should give you what was requested. -- Storm Wrote: Hi Robert, Thanks for your answer but I am not that up there with creating formula with that many variables.... But you have given me an idea that I will investigate further. IF...THEN. Just need to tackle length's and positions Janelle "robert111" wrote: make a lookup table as below 1 one 2 two 3 three etc etc assume number is 1234.56 and is in cell A1 B1= text(A1,"0.00") ie it still LOOKS like 1234.56 C1 =left(B1) ie 1 D1 = mid(A1,2,1) ie 2 and so on You may need to check the length of the text string =len(B1) and where the decimal is =search(B1,".") to make it universal You can either have all these formulas in separate columns or combine them for tidyness. -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=529517 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=529517 |
display a number in one cell as text in more than one cell?
Thanks Brian ;-)
Excellent Works Perfectly. Janelle "Bryan Hessey" wrote: in cell A1 is your amount for a 7 digit dollar amount, in cell B1 put =TEXT(A1,"0000000.99") in cells C1 to C10 put the words Zero One Two ~~ Eight Nine in the first amount-word cell put =OFFSET($C1,MID($B1,COLUMN()-3,1),0,1) and formula drag this to the right. in the .cents cell put =TEXT(MOD(B1,1),".00") this should give you what was requested. -- Storm Wrote: Hi Robert, Thanks for your answer but I am not that up there with creating formula with that many variables.... But you have given me an idea that I will investigate further. IF...THEN. Just need to tackle length's and positions Janelle "robert111" wrote: make a lookup table as below 1 one 2 two 3 three etc etc assume number is 1234.56 and is in cell A1 B1= text(A1,"0.00") ie it still LOOKS like 1234.56 C1 =left(B1) ie 1 D1 = mid(A1,2,1) ie 2 and so on You may need to check the length of the text string =len(B1) and where the decimal is =search(B1,".") to make it universal You can either have all these formulas in separate columns or combine them for tidyness. -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=529517 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=529517 |
display a number in one cell as text in more than one cell?
Good to see, and thanks for your response. -- Storm Wrote: Thanks Bryan ;-) Excellent Works Perfectly. Janelle "Bryan Hessey" wrote: in cell A1 is your amount for a 7 digit dollar amount, in cell B1 put =TEXT(A1,"0000000.99") in cells C1 to C10 put the words Zero One Two ~~ Eight Nine in the first amount-word cell put =OFFSET($C1,MID($B1,COLUMN()-3,1),0,1) and formula drag this to the right. in the .cents cell put =TEXT(MOD(B1,1),".00") this should give you what was requested. -- Storm Wrote: Hi Robert, Thanks for your answer but I am not that up there with creating formula with that many variables.... But you have given me an idea that I will investigate further. IF...THEN. Just need to tackle length's and positions Janelle "robert111" wrote: make a lookup table as below 1 one 2 two 3 three etc etc assume number is 1234.56 and is in cell A1 B1= text(A1,"0.00") ie it still LOOKS like 1234.56 C1 =left(B1) ie 1 D1 = mid(A1,2,1) ie 2 and so on You may need to check the length of the text string =len(B1) and where the decimal is =search(B1,".") to make it universal You can either have all these formulas in separate columns or combine them for tidyness. -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=529517 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=529517 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=529517 |
display a number in one cell as text in more than one cell?
"Storm" wrote: Hi, I need to be able to display a number eg. $18735.67 across mulitple cells as text. I need the numbers to read as follows: ONE EIGHT SEVEN THREE FIVE 67 where the spaces between each number denote separate columns and the cents are displayed as numerical. Any help would be much appreciated. Storm |
| All times are GMT +1. The time now is 08:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com