• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Text To Numbers

YasserKhalil

YasserKhalil

Well-Known Member
Hello everyone
I have an attachment in column 1 I have numbers in the format of text ..
How to convert it to numbers ..?
I tried a lot of methods and none of these methods succeed to convert these numbers
and this is very weird

This link has many methods
-----------------------------
https://support.microsoft.com/en-us/kb/291047#bookmark-2

Can you help me?
 

Attachments

  • Convert Text To Numbers.xlsx
    166.8 KB · Views: 15
Only way I can think of is to create lookup table and use formula to replace each Eastern Arabic character with Arabic Number (as in Western Arabic, 0,1,2,3...).
 
Ok, did some testing. Strings has some unicode characters mixed in there. So my original plan won't work either.

But worked out formula solution.
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8206),""),UNICHAR(8236),""),UNICHAR(8234),""),UNICHAR(32),""))

Process of getting there:
1. Something was off so tested length of string
=LEN(A2)
Which evaluated to 21
However only 9 characters visible on the cell.

2. Tried to clean string using usual methods.
=CLEAN(A2) and =TRIM(A2)
Also tried substituting out CHAR(160) & CHAR(127)
Non worked.

3. On a hunch tested for UNICODE
=UNICODE(LEFT(A2,1))
Which evaluated to 8206

4. Substituted out UNICHAR(8206)
=SUBSTITUTE(A2,UNICHAR(8206),"")

5. Still got error so mapped out each character from string

upload_2016-3-15_19-20-35.png

6. Found trailing UNICHAR(8236) and substituted out. Successfully converted to number.

7. Found further down the list, others that gave error. Repeated step 5 for each one encountered.
 

Attachments

  • Convert Text To Numbers.xlsx
    281.4 KB · Views: 9
Unbelievable .. Thank you very very much Mr. CHihiro for this genius steps to solve the problem .. I have spent two hours trying to solve this problem but I can't do it

Can it be done using UDF function or VBA code?
 
I have devised this UDF function based on the following link

http://www.excelforum.com/excel-pro...sing-chr-to-display-the-character-in-vba.html

Code: 
Function AscWConvert(Rng As Range)
    Dim C, P, Str
   
    If Not IsEmpty(Rng) Then
        For P = 1 To Len(Rng.Value)
            C = AscW(Mid(Rng.Value, P, 1))
            If C - 1632 >= 0 And C - 1632 < 10 Then
                Str = Str & (C - 1632)
            End If
        Next P
       
        AscWConvert = Val(Str)
    Else
        AscWConvert = ""
    End If
End Function

But I still need some fixes for the UDF function to deal with normal text and normal numbers
Texts such as "Ahmed" should be resulted in null I mean ""
Numbers which are normal should be as it is such as 15243 would be 15243
 
Would be something like this.

Code: 
Function AscWConvert(Rng As Range)
    Dim C, P, Str
    If IsNumeric(Rng) Then
        AscWConvert = Val(Rng)
   
    ElseIf Not IsEmpty(Rng) Then
        For P = 1 To Len(Rng.Value)
            C = AscW(Mid(Rng.Value, P, 1))
            If C - 1632 >= 0 And C - 1632 < 10 Then
                Str = Str & (C - 1632)
            End If
        Next P
      If Len(Str) = 0 Then
            AscWConvert = ""
      Else
            AscWConvert = Val(Str)
      End If
    End If
End Function
 
Thank you very very much. I didn't believe it is solved ... you are GENIUS and perfect
Kind Regards Mr. Chihiro
 
Sorry for disturbing you again
Can you have a look at these values
‪‎‎0‎‎5‎1‎‎6‎6‎5‎‎2‎1‎1‎5‬
‪‎‎0‎‎5‎5‎‎8‎6‎1‎‎9‎9‎4‎1‬
‪‎‎0‎‎5‎4‎‎4‎1‎4‎‎1‎1‎1‎5‬
‪‎‎0‎‎5‎4‎‎2‎6‎1‎‎5‎5‎5‎6‬
‪‎‎0‎‎5‎6‎‎8‎6‎9‎‎1‎3‎3‎7‬
‪‎‎0‎‎5‎4‎‎9‎7‎6‎‎6‎9‎1‎5‬

The UDF function gives null "" for these ? How can I fix that?
 
Try this.
Code: 
Function AscWConvert(Rng As Range)
    Dim C, P, Str
    If IsNumeric(Rng) Then
        AscWConvert = Val(Rng)

    ElseIf Not IsEmpty(Rng) Then
        For P = 1 To Len(Rng.Value)
            C = AscW(Mid(Rng.Value, P, 1))
            If C - 1632 >= 0 And C - 1632 < 10 Then
                Str = Str & (C - 1632)
            Else
                Select Case C
                Case 48, 49, 50, 51, 52, 53, 54, 55, 56, 57
                Str = Str & ChrW(C)
                End Select
            End If
          
        Next P
      If Len(Str) = 0 Then
            AscWConvert = ""
      Else
            AscWConvert = Val(Str)
      End If
    End If
End Function

This should work as ChrW from 48 to 57 represents 0 to 9.
 
Last edited:
Thank you very much Mr. Chihiro for this wonderful gift. I really like this UDF function and I have learned a lot in this issue
Thanks a lot for your patience
Best Regards
 
Hi !

Gentlemen,
if you read Select Case VBA inner help and its example,
you could directly write Case 48 To 57 ‼
​
 
What happens when you hit F1 key within a VBA code
with text cursor on a valid statement ?

Check if help on Web is disable (via icon at bottom, depends on version)
and if it remains then reinstall Excel with VBA help option …
​
 

With previous versions, web help can be disabled as I yet wrote …

Maybe it's the same with 2016, I don't know …
​
 
Thanks Mr. Chihiro but I don't mean to disable F1 as a key but to disable web help when pressing F1 within VBE editor and to get help from help files withing the windows ...
 
You must log in or register to reply here.
Back
Top