Solved

DBNum2 in Excel

Posted on 2012-03-22
14
1,063 Views
Last Modified: 2012-03-25
How to use the DBNUm1 / DBNum2 in Excel ? I have keyed the simple conversion but it doesn't work. Any idea ?

TEXT(8,"[DBNum1]")="¿"
TEXT(8,"[DBNum2]")="¿"

Actually, I want to convert numeric amount into text. What's wrong with the string ?
G25 Value : 1178.65
Result : /¿¿¿¿¿6¿5¿            

=IF(-DOLLAR(G25),SUBSTITUTE(SUBSTITUTE(IF(G25<0,"¿",)&TEXT(INT(ABS(G25)+0.5%),"[dbnum2]G/¿¿¿¿¿;;")&TEXT(RIGHT(DOLLAR(G25),2),"[dbnum2]0¿0¿;;¿"),"¿¿",IF(ABS(G25)<1,,"¿")),"¿¿","¿"),"")
0
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
14 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37755834
can you provide a simple example worksheet with what you're attempting?

Dave
0
 

Author Comment

by:AXISHK
ID: 37755989
The formula has been bolded with red color.

Thanks
test.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37755995
What language?

Here's a primer I found on conversions - scroll to the dbNum2 conversion.
http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/index.jsp?topic=%2Fcom.ibm.productivity.tools.help%2Ftext%2Fcommon%2F01%2F05020301.html
Dave
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AXISHK
ID: 37756003
Simplfied Chinese.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37756005
Please advise what you are seeing in cell J27 as I see chinese characters I assume are representing the number.  Is this what I should be seeing?
0
 

Author Comment

by:AXISHK
ID: 37756015
No, they aren't.
Actually, I want to see the Chinese numeric word as xx dollor xx cents, where "dolloar" and "cents" will be displayed in Chinese words.

Thanks
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37756017
So you need help with the words dollars and cents, or just the number formatting as stated in the original question?

E.g., If I show you how to convert a number to simplified chinese using DBNUM can you take it from there?  I don't read Chinese.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37756025
Is this what you're looking for (where you put the chinese words in for dollars and cents?)Is this correct?
Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37756082
Are you there?  I tested online and the simplified chinese seems correct as 700 dollars and 00 cents.  Or, are you trying to say "Seven Hundred Dollars and Zero Cents"?

Please verify as I'm standing by with some alternatives.


I used this where J54 had the value I was testing.

=TEXT(J54,"[DBNum2][$-804]0") & " Dollars and " & RIGHT(TEXT(J54,"[DBNum2][$-804].00"),3) & " cents"
Dave
0
 

Author Comment

by:AXISHK
ID: 37756201
Yes. What's the meaning of" [[$-804]0" ?

Any idea what's wrong with the formula in the Excel file ?

Thanks
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37756330
If the answer is correct, is there a need to "fix" the formula?

804 is the code number for simple Chinese format conversion:

http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx

Cheers,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37756341
Translating all of it to simple chinese...

The revised formula for cell J27 should be:

Guidance
See attached worksheet with J27 updated

Cheers,

Dave
test2.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37756353
Unless I've given you something that is incorrect (though I think your comment "Yes" means it is correct), I'd need help on the characters you have in the formula as I can't translate chinese that fast = would probably take me 30+ minutes to do that.  So if the chinese characters in your formula were converted to english, THEN I could help you parse the formula if what I gave you doesn't suffice.

Also, I think I answered your fundamental question - how to convert a number using the Text function to simple chinese.  by the way, it is impossible to read your original question to see the chinese characters - they all look like upside-down question marks ;)

Do you need additional assistance, or is what I've given you created a direct linkage for you to solve the problem, going forward?  I want to make sure you're getting what you asked for.

Please advise.

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37756382
The google translation of my original solution for 700.00 is:

"SEVEN December dollars and cents December" which I believe is right.

-------------------------
Ok - I gave your formula a shot, and hopefully you find this a help.  Would be great if you tell me how close I got, lol:

The revised formula, parsing all the bits together and using your conjunction terms:
repaired?
And, this could be right but the idioms you're using don't directly translate with google, but here goes:  700.00 translated using this new format to:

"The qi Hong common format yuan qi. December" which could be right too?

See attached.

Dave
test3.xls
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question