Formatting numerals and text numbers between Arabic and Hindi (indic) languages

  • Thread starter Thread starter steveblack2006
  • Start date Start date
S

steveblack2006

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.
 
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))
 
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)
 
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:Arabic_numerals-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?
 
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.
 
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)


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 said:
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


--
Click to expand...
Click to expand...
 
Back
Top