The cells containing multiple languages, I want only the cell only English

Hi Expert ,

I will be very grateful if any one can give the solution :

I have spreadsheet  The cells containing multiple languages with English language, How do I transfer to new cells take English language only.

Thanks
Excel-English-Language.xlsx
Satech JubaraAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ryan ChongConnect With a Mentor Commented:
here you go, try this:
1. open up your Excel file.
2. open the Microsoft Visual Basic for Application window by pressing Alt + F11
3. Go to menu Insert > Module
4. Paste the script above
5. go to your Excel front end, and put formula : =getEnglish(D7)
    * you should able to find that custom formula after you start typing =getEngl....
6. try to save your file with macro enabled feature, like to save as a .xlsm file instead.
0
 
Ryan ChongCommented:
try create a custom function like this and call it as a formula in your target cell:
Function getEnglish(ByVal s As String) As String
    For i = 1 To Len(s)
        Select Case Asc(Mid(s, i, 1))
        Case 65 To 90
            t = t & Mid(s, i, 1)
        Case 97 To 122
            t = t & Mid(s, i, 1)
        End Select
    Next
    getEnglish = t
End Function

Open in new window


=getEnglish(D7)

Open in new window

Excel-English-Language_b.xlsm
0
 
Satech JubaraAuthor Commented:
Ho i can Published  this code to create the function , By steps please
0
 
Saqib Husain, SyedEngineerCommented:
Right-click on the sheet tab name
Select View code
Insert > Module
paste this code in the vba window
close the VBA window
Function english(r As String) As String
    For i = 1 To Len(r)
        Select Case Mid(r, i, 1)
            Case "A" To "Z", "a" To "z", " "
                english = english & Mid(r, i, 1)
        End Select
    Next i
    english = Trim(english)
End Function

Open in new window


Enter this in a cell

=english(d7)
0
 
Satech JubaraAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.