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

 

[Nineball]

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?

Now that you mention it, my triangles are green and they do mean a value stored 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?

In the picture I just uploaded, the warning triangles are green and indicate a value stored as text. I didn't pay attention to your example that they were red. Either way, Text to columns is what I use when I first create the cell. That's how I discovered the ones that are problematic

 

[Nineball]

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.

View attachment 94648

Yes, that works. However, doing this process for thousands of different numbers would be a hell of a lot more work than just typing the numbers into the cell

 

[Akuini]

@Nineball
Did you try the method I suggested in post #10?

 

[Akuini]

Yes, that works. However, doing this process for thousands of different numbers would be a hell of a lot more work than just typing the numbers into the cell

Ok, I suspect the 9 is a unicode character.
Check it like this:
If it's 9 as Ascii character the result should be 57, if not then we can write a code to replace it to 9 .

Book1
	A	B
1	9	57
Sheet1

Cell Formulas
Range	Formula
B1	B1	=UNICODE(A1)

 

[Nineball]

Yes, that works. However, doing this process for thousands of different numbers would be a hell of a lot more work than just typing the numbers into the cell

I just saw your question about how I obtain the 9 in cell A2.

first, I determine the location of the colon and the first space using findb. They might be different in each case. In the example sentence below, the colon is at the 2nd position and the first space is at the 5 position. The first number (which always works properly) is left(cell, colon position-1) which results in a 1 for the below cell. Then to get the number after the colon (the problem one), =mid(cell, colon position+1, (Space Position-1)-Colon Position) which results in 17.

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

Does that make sense? I know it's hard to do these things cold like this

 

[KRice]

Yes, thank you...based on what you posted, I figured that's how you were extracting the numbers. Have you seen the suggestion from @Akuini to check the unicode? I think that looks promising.

 

[Nineball]

Ok, I suspect the 9 is a unicode character.
Check it like this:
If it's 9 as Ascii character the result should be 57, if not then we can write a code to replace it to 9 .

Book1
	A	B
1	9	57
Sheet1

Cell Formulas
Range	Formula
B1	B1	=UNICODE(A1)

the result of the unicode for the 17 is 49. =unicode(cell containing 17) is 49

 

[Nineball]

@Nineball
Did you try the method I suggested in post #10?

yes, but would be more labor intensive to find and replace all of the values than it would be to just input the numbers manually. I think you have something with the ascii unicode thing. What would be the formula to change it from the ascii value back to the proper number?

 

[Akuini]

the result of the unicode for the 17 is 49. =unicode(cell containing 17) is 49

That's not what I got.
I copied your example from post #9 then pasted it in a text editor then from there copy 17 to excel, here's what I got:

Book1
	A	B
1	لەن	1604
Sheet1

Cell Formulas
Range	Formula
B1	B1	=UNICODE(A1)

so 17 became لەن

 

[Nineball]

sorry if my answers are blended with the wrong questions. I'm new to this and have been working on this for a while