Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Converting Numeric values to Text
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 195
Default Converting Numeric values to Text
Hi,

I have made a nested fomula which comverts numeric value to words (for
example - 101 to One Hundred and One). For this I had numbers from 1 to
20 in column A1 till A20 and 30, 40, 50... till 90 from A21 through
A27. And the corresponding value in words to the next column i.e., B1
to B27.
And at range C1: D2 as
3 | Hundred
4 | Thousand

I have four nested formula named type1, type2, type3 and type4

type1 as :

=IF(LEN(E9)=4,VLOOKUP(VALUE(LEFT(E9,1)),A1:B9,2,FA LSE)&"
"&VLOOKUP(LEN(E9),C2:D2,2,FALSE)&"
"&IF(ISERROR(VLOOKUP(VALUE(LEFT(MID(E9,2,3),1)),A1 :B9,2,FALSE)&"
"&VLOOKUP(LEN(MID(E9,2,3)),C1:D2,2,FALSE)),"",VLOO KUP(VALUE(LEFT(MID(E9,2,3),1)),A1:B9,2,FALSE)&"
"&VLOOKUP(LEN(MID(E9,2,3)),C1:D2,2,FALSE))&"
"&IF(ISERROR(IF(AND(VALUE(MID(E9,4,1))<0,VALUE(MI D(E9,3,1))<1),VLOOKUP(VALUE(MID(E9,3,1)&0),A20:B2 7,2,FALSE),"")),"",IF(AND(VALUE(MID(E9,4,1))<0,VA LUE(MID(E9,3,1))<1),VLOOKUP(VALUE(MID(E9,3,1)&0), A20:B27,2,FALSE),""))&"
"&IF(ISERROR(IF(VALUE(MID(E9,3,1))<1,VLOOKUP(VALU E(MID(E9,4,1)),A1:B9,2,FALSE))),"",IF(VALUE(MID(E9 ,3,1))<1,"and
"&VLOOKUP(VALUE(MID(E9,4,1)),A1:B9,2,FALSE),"" ))&"
"&IF(ISERROR(VLOOKUP(VALUE(MID(E9,3,2)),A10:B27,2, FALSE)),"","and
"&VLOOKUP(VALUE(MID(E9,3,2)),A10:B27,2,FALSE)) ,"")

type2 as:

=IF(LEN(E9)=3,VLOOKUP(VALUE(LEFT(E9,1)),A1:B9,2,FA LSE)&"
"&VLOOKUP(LEN(E9),C1:D1,2,FALSE)&"
"&IF(ISERROR(IF(VALUE(MID(E9,3,1))<0,VLOOKUP(VALU E(MID(E9,2,1)&0),A20:B27,2,FALSE))),"",IF(VALUE(MI D(E9,3,1))<0,"and
"&VLOOKUP(VALUE(MID(E9,2,1)&0),A20:B27,2,FALSE),"" ))&"
"&IF(ISERROR(IF(VALUE(MID(E9,2,1))<1,VLOOKUP(VALU E(MID(E9,3,1)),A1:B9,2,FALSE),"")),"",IF(VALUE(MID (E9,2,1))<1,VLOOKUP(VALUE(MID(E9,3,1)),A1:B9,2,FA LSE),""))&"
"&IF(ISERROR(VLOOKUP(VALUE(MID(E9,2,2)),A10:B27,2, FALSE)),"","and
"&VLOOKUP(VALUE(MID(E9,2,2)),A10:B27,2,FALSE)) ,"")

type3 as:

=IF(LEN(E9)=2,IF(ISERROR(IF(VALUE(RIGHT(E9,1))0,V LOOKUP(VALUE(LEFT(E9,1)&0),A20:B27,2,FALSE),""))," ",IF(VALUE(RIGHT(E9,1))0,VLOOKUP(VALUE(LEFT(E9,1) &0),A20:B27,2,FALSE),""))&"
"&IF(ISERROR(IF(VALUE(LEFT(E9,1))<1,VLOOKUP(VALUE (RIGHT(E9,1)),A1:B9,2,FALSE),"")),"",IF(VALUE(LEFT (E9,1))<1,VLOOKUP(VALUE(RIGHT(E9,1)),A1:B9,2,FALS E),""))&"
"&IF(ISERROR(IF(VALUE(LEFT(E9,1))<0,VLOOKUP(E9,A1 0:B27,2,FALSE))),"",IF(VALUE(LEFT(E9,1))<0,VLOOKU P(E9,A10:B27,2,FALSE))),"")

and type4 as:

=IF(LEN(E9)=1,VLOOKUP(E9,A1:B9,2,FALSE),"")

and these 4 named formulas as:

=type1&type2&type3&type4

Everything is working fine. Just sometimes an extra space appears
between the words. I know why it is appearing, but I couldn't be able
to fix it. Also my formula doesn't work with decimals. It treats them
as another character and give the wrong output. Lastly, my formula
works for 4 digit numbers only.

Can you remove the extra spaces between words and make it for decimals
too.

Thanks,

Shail

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,339
Default Converting Numeric values to Text
Why re-invent the wheel?

Look at SPELLNUMBER ...

http://support.microsoft.com/default...b;en-us;213360

"shail" wrote:

Hi,

I have made a nested fomula which comverts numeric value to words (for
example - 101 to One Hundred and One). For this I had numbers from 1 to
20 in column A1 till A20 and 30, 40, 50... till 90 from A21 through
A27. And the corresponding value in words to the next column i.e., B1
to B27.
And at range C1: D2 as
3 | Hundred
4 | Thousand

I have four nested formula named type1, type2, type3 and type4

type1 as :

=IF(LEN(E9)=4,VLOOKUP(VALUE(LEFT(E9,1)),A1:B9,2,FA LSE)&"
"&VLOOKUP(LEN(E9),C2:D2,2,FALSE)&"
"&IF(ISERROR(VLOOKUP(VALUE(LEFT(MID(E9,2,3),1)),A1 :B9,2,FALSE)&"
"&VLOOKUP(LEN(MID(E9,2,3)),C1:D2,2,FALSE)),"",VLOO KUP(VALUE(LEFT(MID(E9,2,3),1)),A1:B9,2,FALSE)&"
"&VLOOKUP(LEN(MID(E9,2,3)),C1:D2,2,FALSE))&"
"&IF(ISERROR(IF(AND(VALUE(MID(E9,4,1))<0,VALUE(MI D(E9,3,1))<1),VLOOKUP(VALUE(MID(E9,3,1)&0),A20:B2 7,2,FALSE),"")),"",IF(AND(VALUE(MID(E9,4,1))<0,VA LUE(MID(E9,3,1))<1),VLOOKUP(VALUE(MID(E9,3,1)&0), A20:B27,2,FALSE),""))&"
"&IF(ISERROR(IF(VALUE(MID(E9,3,1))<1,VLOOKUP(VALU E(MID(E9,4,1)),A1:B9,2,FALSE))),"",IF(VALUE(MID(E9 ,3,1))<1,"and
"&VLOOKUP(VALUE(MID(E9,4,1)),A1:B9,2,FALSE),"" ))&"
"&IF(ISERROR(VLOOKUP(VALUE(MID(E9,3,2)),A10:B27,2, FALSE)),"","and
"&VLOOKUP(VALUE(MID(E9,3,2)),A10:B27,2,FALSE)) ,"")

type2 as:

=IF(LEN(E9)=3,VLOOKUP(VALUE(LEFT(E9,1)),A1:B9,2,FA LSE)&"
"&VLOOKUP(LEN(E9),C1:D1,2,FALSE)&"
"&IF(ISERROR(IF(VALUE(MID(E9,3,1))<0,VLOOKUP(VALU E(MID(E9,2,1)&0),A20:B27,2,FALSE))),"",IF(VALUE(MI D(E9,3,1))<0,"and
"&VLOOKUP(VALUE(MID(E9,2,1)&0),A20:B27,2,FALSE),"" ))&"
"&IF(ISERROR(IF(VALUE(MID(E9,2,1))<1,VLOOKUP(VALU E(MID(E9,3,1)),A1:B9,2,FALSE),"")),"",IF(VALUE(MID (E9,2,1))<1,VLOOKUP(VALUE(MID(E9,3,1)),A1:B9,2,FA LSE),""))&"
"&IF(ISERROR(VLOOKUP(VALUE(MID(E9,2,2)),A10:B27,2, FALSE)),"","and
"&VLOOKUP(VALUE(MID(E9,2,2)),A10:B27,2,FALSE)) ,"")

type3 as:

=IF(LEN(E9)=2,IF(ISERROR(IF(VALUE(RIGHT(E9,1))0,V LOOKUP(VALUE(LEFT(E9,1)&0),A20:B27,2,FALSE),""))," ",IF(VALUE(RIGHT(E9,1))0,VLOOKUP(VALUE(LEFT(E9,1) &0),A20:B27,2,FALSE),""))&"
"&IF(ISERROR(IF(VALUE(LEFT(E9,1))<1,VLOOKUP(VALUE (RIGHT(E9,1)),A1:B9,2,FALSE),"")),"",IF(VALUE(LEFT (E9,1))<1,VLOOKUP(VALUE(RIGHT(E9,1)),A1:B9,2,FALS E),""))&"
"&IF(ISERROR(IF(VALUE(LEFT(E9,1))<0,VLOOKUP(E9,A1 0:B27,2,FALSE))),"",IF(VALUE(LEFT(E9,1))<0,VLOOKU P(E9,A10:B27,2,FALSE))),"")

and type4 as:

=IF(LEN(E9)=1,VLOOKUP(E9,A1:B9,2,FALSE),"")

and these 4 named formulas as:

=type1&type2&type3&type4

Everything is working fine. Just sometimes an extra space appears
between the words. I know why it is appearing, but I couldn't be able
to fix it. Also my formula doesn't work with decimals. It treats them
as another character and give the wrong output. Lastly, my formula
works for 4 digit numbers only.

Can you remove the extra spaces between words and make it for decimals
too.

Thanks,

Shail


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 195
Default Converting Numeric values to Text
Hi Topppers,

I wanted to do it without the VBA codes. I have been watching your (and
Bob Phillips, David Billigmeier, Leo Heuser's ) posts for long and I
need your lelp in it. Also please tell me if there is a way simpler
than what I have developed.

Thanks Toppers,

Shail


Toppers wrote:
Why re-invent the wheel?

Look at SPELLNUMBER ...

http://support.microsoft.com/default...b;en-us;213360


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
clicking cell containing text, and a numeric value appear in anoth Graham Allen Excel Discussion (Misc queries) 1 September 4th 06 09:22 PM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 02:03 AM
Count Intervals of 2 Numeric values in same Row and Return Count across Row Sam via OfficeKB.com Excel Worksheet Functions 12 September 24th 05 10:58 PM
Test IF Two Numeric Values Match (from two separate Dynamic Ranges) Sam via OfficeKB.com Excel Worksheet Functions 3 August 14th 05 12:20 AM
Assigning text values to numeric fields ab565 Excel Discussion (Misc queries) 1 August 11th 05 10:49 PM


All times are GMT +1. The time now is 06:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement