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

[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.

 

[Nigel]

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))

 

[Bob Phillips]

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)

 

[steveblack2006]

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?

 

[steveblack2006]

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]

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.

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


--

 

[steveblack2006]

Dear Bob

Thanks a lot. That worked great.

Regards