Solved

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

Posted on 2016-11-03
5
45 Views
Last Modified: 2016-11-03
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
0
Comment
Question by:Satech Jubara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41871726
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
 

Author Comment

by:Satech Jubara
ID: 41871733
Ho i can Published  this code to create the function , By steps please
0
 
LVL 51

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41871745
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41871749
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
 

Author Closing Comment

by:Satech Jubara
ID: 41871752
Thanks
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question