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

 

[Akuini]

Ok, the google link doesn't work but dropbox link does, so it's ok.

 

[Nineball]

Ok, the google link doesn't work but dropbox link does, so it's ok.

excellent

 

[KRice]

Thank you. Unfortunately, I don't think this will work for you then, but thought I'd post it in case you see something that offers another idea:

Book4
	A	B	C	D	E
10	1st number	2nd number		Modified Excerpt	Original
11	1	17		1Z17	1:17‏ خۇدا بۇلارنى يەرگە يورۇقلۇق بېرىپ، كۈندۈز بىلەن كېچىنى باشقۇرۇپ، يورۇقلۇق بىلەن قاراڭغۇلۇقنى ئايرىسۇن دەپ ئاسمانلارنىڭ گۈمبىزىگە ئورۇنلاشتۇردى. خۇدا بۇنىڭ ياخشى بولغانلىقىنى كۆردى.
12	1	17
Sheet5

Cell Formulas
Range	Formula
A11:B11	A11	=TEXTSPLIT(D11,"Z")+0
D11	D11	=ALPHANUMONLY(SUBSTITUTE(E11,":","Z"))
A12	A12	=LEFT(D11,FIND("Z",D11)-1)+0
B12	B12	=RIGHT(D11,LEN(D11)-FIND("Z",D11))+0
Dynamic array formulas.

This relies on several LAMBDA functions...and I don't think Excel 2021 has them, does it? This approach was contributed by @lrobbo314 at...

Removing Non-Alphanumeric Characters

Removing Non-Alphanumeric Characters. Been trying to use the code in thread at Removing Non-Alphanumeric Characters (without using macros or script!) But I cannot get anything to work as in the code associated with "Function CleanCode" also the code "=UPPER(CLEAN(SUBSTITUTE(" I want to...

www.mrexcel.com

This involves setting up the three LAMBDA functions (details in the link) in the Name Manager to essentially create a new function to remove all characters except alphanumerics. So I substitute a "Z" for the colon, apply the LAMBDA function to extract the verse numbers, and then split that text apart using either of two approaches.

 

[Nineball]

Thank you. Unfortunately, I don't think this will work for you then, but thought I'd post it in case you see something that offers another idea:

Book4
	A	B	C	D	E
10	1st number	2nd number		Modified Excerpt	Original
11	1	17		1Z17	1:17‏ خۇدا بۇلارنى يەرگە يورۇقلۇق بېرىپ، كۈندۈز بىلەن كېچىنى باشقۇرۇپ، يورۇقلۇق بىلەن قاراڭغۇلۇقنى ئايرىسۇن دەپ ئاسمانلارنىڭ گۈمبىزىگە ئورۇنلاشتۇردى. خۇدا بۇنىڭ ياخشى بولغانلىقىنى كۆردى.
12	1	17
Sheet5

Cell Formulas
Range	Formula
A11:B11	A11	=TEXTSPLIT(D11,"Z")+0
D11	D11	=ALPHANUMONLY(SUBSTITUTE(E11,":","Z"))
A12	A12	=LEFT(D11,FIND("Z",D11)-1)+0
B12	B12	=RIGHT(D11,LEN(D11)-FIND("Z",D11))+0
Dynamic array formulas.

This relies on several LAMBDA functions...and I don't think Excel 2021 has them, does it? This approach was contributed by @lrobbo314 at...

Removing Non-Alphanumeric Characters

Removing Non-Alphanumeric Characters. Been trying to use the code in thread at Removing Non-Alphanumeric Characters (without using macros or script!) But I cannot get anything to work as in the code associated with "Function CleanCode" also the code "=UPPER(CLEAN(SUBSTITUTE(" I want to...

www.mrexcel.com

This involves setting up the three LAMBDA functions (details in the link) in the Name Manager to essentially create a new function to remove all characters except alphanumerics. So I substitute a "Z" for the colon, apply the LAMBDA function to extract the verse numbers, and then split that text apart using either of two approaches.

my version doesn't have TEXTSPLIT or ALPHANUMONLY

 

[KRice]

Okay...TEXTSPLIT is a native function in 365, but there are work-arounds without it (shown in A12:B12). ALPHANUMONLY is the name assigned to one of the three LAMDA functions that would be set up in the Name Manager, but I don't think Excel 2021 includes LAMBDA. To check, go to an empty cell and begin typing =LA to see if the autocomplete feature suggests the LAMBDA function (assuming you have the autocomplete feature active).

 

[Nineball]

Okay...TEXTSPLIT is a native function in 365, but there are work-arounds without it (shown in A12:B12). ALPHANUMONLY is the name assigned to one of the three LAMDA functions that would be set up in the Name Manager, but I don't think Excel 2021 includes LAMBDA. To check, go to an empty cell and begin typing =LA to see if the autocomplete feature suggests the LAMBDA function (assuming you have the autocomplete feature active).

the only thing that comes up is =large.

I very much appreciate your efforts here. I'm working on a project where I encounter right to left languages though infrequently. I will just live with this limitation. It just pisses me off to come across a problem that I cannot solve in Excel. I blame it on Excel. Have you ever tried creating paragraphs by concatenating cells for Arabic? The results are whacked. maybe if I bought Excel in Egypt I'd have better luck. Anyway, cheers and thanks again.

 

[KRice]

Try the green cells...a VBA solution:

Book4
	A	B	C	D	E
13	1st number	2nd number		Modified Excerpt	Original
14	1	17		1Z17	1:17‏ خۇدا بۇلارنى يەرگە يورۇقلۇق بېرىپ، كۈندۈز بىلەن كېچىنى باشقۇرۇپ، يورۇقلۇق بىلەن قاراڭغۇلۇقنى ئايرىسۇن دەپ ئاسمانلارنىڭ گۈمبىزىگە ئورۇنلاشتۇردى. خۇدا بۇنىڭ ياخشى بولغانلىقىنى كۆردى.
Sheet5

Cell Formulas
Range	Formula
A14	A14	=LEFT(D14,FIND("Z",D14)-1)+0
B14	B14	=RIGHT(D14,LEN(D14)-FIND("Z",D14))+0
D14	D14	=AlphaNumericOnly(SUBSTITUTE(E11,":","Z"))

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

 

[Nineball]

I don't know if you guys noticed but it doesn't happen on every row. The only thing I see in common with the 3 rows with the problem is that there is a period on the very right.

 

[Nineball]

Try the green cells...a VBA solution:

Book4
	A	B	C	D	E
13	1st number	2nd number		Modified Excerpt	Original
14	1	17		1Z17	1:17‏ خۇدا بۇلارنى يەرگە يورۇقلۇق بېرىپ، كۈندۈز بىلەن كېچىنى باشقۇرۇپ، يورۇقلۇق بىلەن قاراڭغۇلۇقنى ئايرىسۇن دەپ ئاسمانلارنىڭ گۈمبىزىگە ئورۇنلاشتۇردى. خۇدا بۇنىڭ ياخشى بولغانلىقىنى كۆردى.
Sheet5

Cell Formulas
Range	Formula
A14	A14	=LEFT(D14,FIND("Z",D14)-1)+0
B14	B14	=RIGHT(D14,LEN(D14)-FIND("Z",D14))+0
D14	D14	=AlphaNumericOnly(SUBSTITUTE(E11,":","Z"))

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

I kind of understand. Why is D14 referencing E11?

 

[Nineball]

Thanks guys. I'm exhausted from this. Again, thank you and good night