Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting numerals and text numbers between Arabic and Hindi (indic) languages
Hi
I am trying to format some cells which contain numbers and numbers stored as text to another language (Arabic language). So I have to format the Arabic numbers to what is called Hindi or Indic numbers (that which is used in Arabic language). To give an example of what I want: A B 1 400 =A1 (but formatted as Hindi or Indic numeral using "[$-2000000]0.00" 2 15-12-2006 =A2 (but formatted as Hindi or Indic numeral "[$-2000000]dd-mm-yyyy" 3 161-12 =A3 (formatting do not work here - "[$-2000000]@" The formatting of B1 and B2 are done using the following VBA code. Sheet1.Range("B1").NumberFormat = "[$-2000000]0.00" Sheet1.Range("B2").NumberFormat = "[$-2000000]dd-mm-yyyy" The above works fine shows the data in B1 and B2 as Hindi/Indic numerals because A1 is formatted as number A2 is formatted as Date. Now if A3 is formatted as text and the A3 data is 161-12 Then B3 formatted using the following code Sheet1.Range("B3").NumberFormat = "[$-2000000]@" do not work. The numbers are displayed as Arabic numbers only. Is there a way numbers separated by a hyphen (-) like 150-140 can be formatted Hindi Indic numerals? Any help will be appreciated. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting numerals and text numbers between Arabic and Hindi (indic) languages
I think you will need to split the two values before and after the hyphen
then format separately and recombine as a text string like....... (this format a string like 123-140 to $123.00-$140.00 - not sure what formatting you need for local language, HTH? =CONCATENATE(DOLLAR(LEFT(A1,FIND("-",A1,1)-1),2),"-",DOLLAR(MID(A1,FIND("-",A1,1)+1,LEN(A1)-FIND("-",A1,1)),2)) -- Cheers Nigel wrote in message oups.com... Hi I am trying to format some cells which contain numbers and numbers stored as text to another language (Arabic language). So I have to format the Arabic numbers to what is called Hindi or Indic numbers (that which is used in Arabic language). To give an example of what I want: A B 1 400 =A1 (but formatted as Hindi or Indic numeral using "[$-2000000]0.00" 2 15-12-2006 =A2 (but formatted as Hindi or Indic numeral "[$-2000000]dd-mm-yyyy" 3 161-12 =A3 (formatting do not work here - "[$-2000000]@" The formatting of B1 and B2 are done using the following VBA code. Sheet1.Range("B1").NumberFormat = "[$-2000000]0.00" Sheet1.Range("B2").NumberFormat = "[$-2000000]dd-mm-yyyy" The above works fine shows the data in B1 and B2 as Hindi/Indic numerals because A1 is formatted as number A2 is formatted as Date. Now if A3 is formatted as text and the A3 data is 161-12 Then B3 formatted using the following code Sheet1.Range("B3").NumberFormat = "[$-2000000]@" do not work. The numbers are displayed as Arabic numbers only. Is there a way numbers separated by a hyphen (-) like 150-140 can be formatted Hindi Indic numerals? Any help will be appreciated. Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting numerals and text numbers between Arabic and Hindi (indic) languages
The problem is that 162-15 is not a number but text, and formatting doesn't
work on text. Either enter the number without the - and use a custom format of "[$-2000000]#0-00", or use the VBA to strip it out and format it With Sheet1.Range("B3") .Value = Replace(.Value, "-", "") .NumberFormat = "[$-2000000]#0-00" End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message oups.com... Hi I am trying to format some cells which contain numbers and numbers stored as text to another language (Arabic language). So I have to format the Arabic numbers to what is called Hindi or Indic numbers (that which is used in Arabic language). To give an example of what I want: A B 1 400 =A1 (but formatted as Hindi or Indic numeral using "[$-2000000]0.00" 2 15-12-2006 =A2 (but formatted as Hindi or Indic numeral "[$-2000000]dd-mm-yyyy" 3 161-12 =A3 (formatting do not work here - "[$-2000000]@" The formatting of B1 and B2 are done using the following VBA code. Sheet1.Range("B1").NumberFormat = "[$-2000000]0.00" Sheet1.Range("B2").NumberFormat = "[$-2000000]dd-mm-yyyy" The above works fine shows the data in B1 and B2 as Hindi/Indic numerals because A1 is formatted as number A2 is formatted as Date. Now if A3 is formatted as text and the A3 data is 161-12 Then B3 formatted using the following code Sheet1.Range("B3").NumberFormat = "[$-2000000]@" do not work. The numbers are displayed as Arabic numbers only. Is there a way numbers separated by a hyphen (-) like 150-140 can be formatted Hindi Indic numerals? Any help will be appreciated. Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting numerals and text numbers between Arabic and Hindi (indic) languages
Nigel thanks for the quick reply.
What I need to do is to change the numbers into a different language, in this case to Arabic language numeral (for some reason called as Hindi or Arabic-Indic numerals). Link below will show you Arabic-Indic numerals. http://en.wikipedia.org/wiki/Image:A...umerals-en.svg So all I need to do is to format numerals we normally use (123 etc., but called Arabic or European numerals) to Arabic-indic as shown at above link. So 450-500 when formatted should remain the same number and format, but in Arabic-Indic numeral. VBA code I used formats them correctly as long as original cell is formatted as number or a date or anything that is explicitly a number in Excel. I am trying to see something like 469-569 etc could be formatted too. I tried splitting the above number in to two cell as 469 (C1) and 569(D1) using formula =(LEFT(A1,FIND("-",A1,1)-1)) etc. But when I tried formatting the above using Sheet1.Range("C1").NumberFormat = "[$-2000000]0.00" It wont change the language. But if I directly enter a number into a cell and then use above VBA code, it gets formatted... Another issue is I cannot CONCATENATE two numbers in H1 and H2 formatted as Arabic-Indic using something like =H1&"-"&H2. They become Arabic (Or European) numeral when combined, even if it formatted as "[$-2000000]0.00" Hmm... don't know what to do.. Thanks Nigel..anymore insights? Nigel wrote: I think you will need to split the two values before and after the hyphen then format separately and recombine as a text string like....... (this format a string like 123-140 to $123.00-$140.00 - not sure what formatting you need for local language, HTH? =CONCATENATE(DOLLAR(LEFT(A1,FIND("-",A1,1)-1),2),"-",DOLLAR(MID(A1,FIND("-",A1,1)+1,LEN(A1)-FIND("-",A1,1)),2)) -- Cheers Nigel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting numerals and text numbers between Arabic and Hindi (indic) languages
Thanks Bob
That worked. Only a small niggle. The original numbers vary in their position of hyphen 400-3000 20-300 3000-59 1000-529 Etc. So how do I ensure hyphen gets placed at same location when reformatted? Is there a way? Thanks a lot for the help all you are giving. Bob Phillips wrote: The problem is that 162-15 is not a number but text, and formatting doesn't work on text. Either enter the number without the - and use a custom format of "[$-2000000]#0-00", or use the VBA to strip it out and format it With Sheet1.Range("B3") .Value = Replace(.Value, "-", "") .NumberFormat = "[$-2000000]#0-00" End With -- --- HTH Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting numerals and text numbers between Arabic and Hindi (indic) languages
The custom format is not possible, but it can be done with VBA
Dim iPos As Long Dim sFormat As String With Sheet1.Range("B3") iPos = InStr(.Value, "-") sFormat = Application.Rept("0", iPos - 1) & "-" & _ Application.Rept("0", Len(.Value) - iPos) .Value = Replace(.Value, "-", "") .NumberFormat = "[$-2000000]" & sFormat End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message oups.com... Thanks Bob That worked. Only a small niggle. The original numbers vary in their position of hyphen 400-3000 20-300 3000-59 1000-529 Etc. So how do I ensure hyphen gets placed at same location when reformatted? Is there a way? Thanks a lot for the help all you are giving. Bob Phillips wrote: The problem is that 162-15 is not a number but text, and formatting doesn't work on text. Either enter the number without the - and use a custom format of "[$-2000000]#0-00", or use the VBA to strip it out and format it With Sheet1.Range("B3") .Value = Replace(.Value, "-", "") .NumberFormat = "[$-2000000]#0-00" End With -- --- HTH Bob |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting numerals and text numbers between Arabic and Hindi (indic) languages
Dear Bob
Thanks a lot. That worked great. Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONVERT ROMAN TO ARABIC NUMERALS | Excel Worksheet Functions | |||
how do i write hindi numerals in excel 2003 | Excel Discussion (Misc queries) | |||
Arabic / Hindi Numerals | Excel Programming | |||
Enabling Arabic numerals in Excel | Excel Discussion (Misc queries) | |||
how do i get numbers in arabic not hindi? | Excel Worksheet Functions |