Need to convert a number from a parsed Arabic sentence to a number

[Nineball]

After parsing an Arabic sentence that contains a number, the number appears at the right hand side of the cell. I believe the number appears as text. Any formula on the cell results in #VALUE.
I haven't found any function that will change it to a number. Any suggestions? thanks

 

[KRice]

Try multiplying it by 1 to coerce it to a number.

 

[Nineball]

Try multiplying it by 1 to coerce it to a number.

I tried that. No dice. It is not a number and any formula that works on numbers only causes the error #VALUE

 

[KRice]

A numeric character that is formatted as text will be coerced to a numeric data type when a mathematical operation is performed on it (see example, where the TYPE function shows the transition from text to number).

Book4
	A	B
1	text	number
2	5	5
3	2	1
4	5	5
5	1	1
Sheet3

Cell Formulas
Range	Formula
A3:B3,A5:B5	A3	=TYPE(A2)
A4:B4	A4	=A2*1

Have you checked the length of the contents where "9" appears...e.g. =LEN(A2)
I'm curious if the result is 1 or something different...indicating that some other non-displaying characters might be present.

 

[Nineball]

I tried that. No dice. It is not a number and any formula that works on numbers only causes the error #VALUE

I think that Excel thinks it is a text character even though I see it as a number. so doing anything like text to columns or a numbervalue function or any other numeric function doesn't work on this. I have had to type the number in replacing the character. Unfortunately, I have thousands of these and don't want to populate the cells manually.

 

[KRice]

Did you see the question I asked at the end of my last post...about checking the length of the text string?

 

[Nineball]

A numeric character that is formatted as text will be coerced to a numeric data type when a mathematical operation is performed on it (see example, where the TYPE function shows the transition from text to number).

Book4
	A	B
1	text	number
2	5	5
3	2	1
4	5	5
5	1	1
Sheet3

Cell Formulas
Range	Formula
A3:B3,A5:B5	A3	=TYPE(A2)
A4:B4	A4	=A2*1

Have you checked the length of the contents where "9" appears...e.g. =LEN(A2)
I'm curious if the result is 1 or something different...indicating that some other non-displaying characters might be present.

the length of the content is 1. In your example, see how the little red triangle appears in the upper left corner when a numeric value is set as text? Mine doesn't have that. I have been working with spreadsheets (Lotus 123, Quattro pro, and Excel) since that late 80s. In all that time, I haven't experienced this. Perhaps Excel sees the character as an Arabic or other left to right character and not as a number. I have no idea. thanks for helping though. I appreciate it

 

[KRice]

In your example, see how the little red triangle appears in the upper left corner when a numeric value is set as text?

I don't think that's what the red symbols mean. The TYPE formula on rows 3 and 5 is designed to return a numeric code indicating whether the argument passed to it is text (2) or numeric (1). So those cells contain numbers are are formatted as such. The LEN function you've reported on should count nonprinting characters that sometimes cause this type of problem, and it sounds as if there are none in the cells. How are you obtaining the result of "9" in cell A2?

Also, have you tried the Text to Column wizard...to see if the 9 can be automatically reinterpreted as a numeric 9?

 

[Nineball]

the length of the content is 1. In your example, see how the little red triangle appears in the upper left corner when a numeric value is set as text? Mine doesn't have that. I have been working with spreadsheets (Lotus 123, Quattro pro, and Excel) since that late 80s. In all that time, I haven't experienced this. Perhaps Excel sees the character as an Arabic or other left to right character and not as a number. I have no idea. thanks for helping though. I appreciate it

I can't upload a mini sheet. I haven't used the plugin before. I'm upload a picture that you should be able to see what I'm talking about. Here is a sentence you can mess around with.

1:17‏ خۇدا بۇلارنى يەرگە يورۇقلۇق بېرىپ، كۈندۈز بىلەن كېچىنى باشقۇرۇپ، يورۇقلۇق بىلەن قاراڭغۇلۇقنى ئايرىسۇن دەپ ئاسمانلارنىڭ گۈمبىزىگە ئورۇنلاشتۇردى. خۇدا بۇنىڭ ياخشى بولغانلىقىنى كۆردى.​

I've struggled with right to left languages in Excel. Concatenating sentences to make paragraphs usually produces a mess.

 

[Akuini]

Let's try something simple first:
select the cell with the 9 > Ctrl+C > Ctrl+H > in Replace tab put cursor in Find What box then paste (Ctrl+V) > in the Replace with box, type 9 > Replace all
Then see how it works now.