Formula to remove letter and leave numbers and chinese characters

T

tenluvdolphins

New Member
Joined
Mar 19, 2013
Messages
4
Good day,

I hope someone can help me.

I need a formula to use on MS Excel 2007 to remove the English letters from a cell A1 and return only the numbers(if any) & Chinese characters to B1.

Please refer to the able below. I would appreciate an immediate response and would be grateful fr any help provided. Thank you.

Example:
[TABLE="width: 698"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]so l首先欢迎大家收听我们的科学与烹饪系列[/TD]
[TD]首先欢迎大家收听我们的科学与烹饪系列[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]讲座的最后一次报告 有点难过这是最后一次了[/TD]
[TD]讲座的最后一次报告 有点难过这是最后一次了[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]这次我们穿着围裙 每周都出席听课的人应该记得[/TD]
[TD]这次我们穿着围裙 每周都出席听课的人应该记得[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]我们每周都有公式[/TD]
[TD]我们每周都有公式[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]we h还要对每周公式鼓掌[/TD]
[TD]还要对每周公式鼓掌[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]300ove 300磅超过300磅 至少300磅蛋白质[/TD]
[TD]300 300磅超过300磅 至少300磅蛋白质[/TD]
[/TR]
</tbody>[/TABLE]

 
Sort by date Sort by votes
Have you thought about using code?
Something like

Code: 
Sub LetterFindandReplace()
'LetterFindandReplace
    
    Columns("A:A").Select
    Selection.Copy
    Columns("B:B").Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    Columns("B:B").Select


    Cells.Replace What:="a", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="b", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="c", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End Sub

etc


Don't forget to save your workbook before you do this because you won't be able to undo.
 
Upvote 0
Sorry that should be:

Code: 
Selection.Replace What:="a", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

If you do what I had previously written it will do it for the whole sheet not just column B
 
Upvote 0
Jugantarji said:
Sorry that should be:

Code: 
Selection.Replace What:="a", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

If you do what I had previously written it will do it for the whole sheet not just column B
Click to expand...

Jugantarji
user-offline.png
, thank you so much for your reply. Truthfully, not familiar with using codes on Excel. May I ask how do I do that?
 
Upvote 0
I apologize slight correction. Th source cell is from Column F then the result should appear in the cell of the same row under Column G.
 
Upvote 0
tenluvdolphins said:
I apologize slight correction. Th source cell is from Column F then the result should appear in the cell of the same row under Column G.
Click to expand...

Not to worry if you press Alt+F11 it will bring up your code window and you can copy this complete code into your Visual Basic Window. There is also a button on the 2010 version of excel under view. (Not sure about 2007)

Code: 
Sub LetterFindandReplace()
'LetterFindandReplace
ScreenUpdating = False
    Columns("F:F").Select
    Selection.Copy
    Columns("G:G").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("G1").Select
    Columns("G:G").Select
    
   Selection.Replace What:="a", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="b", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="c", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="d", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="e", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="f", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="g", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="i", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="j", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="k", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="l", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="m", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="n", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="o", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="p", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="q", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="r", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="s", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="t", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="u", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="v", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="w", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="x", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="y", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="z", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("A1").Select
    End Sub

The first bit is selecting the relevant column and pasting into the column next door the second bit is just a find and replace of all the letters of the alphabet.

The way I learned code was by recording a few macros and going back to the code to see how excel interpreted the code, and then reading up once I felt happy with this. There are some interesting threads about learning code on this site as well (People might snort at my method).
 
Upvote 0
Jugantarji said:
Not to worry if you press Alt+F11 it will bring up your code window and you can copy this complete code into your Visual Basic Window. There is also a button on the 2010 version of excel under view. (Not sure about 2007)

Code: 
Sub LetterFindandReplace()
'LetterFindandReplace
ScreenUpdating = False
    Columns("F:F").Select
    Selection.Copy
    Columns("G:G").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("G1").Select
    Columns("G:G").Select
    
   Selection.Replace What:="a", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="b", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="c", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="d", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="e", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="f", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="g", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="i", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="j", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="k", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="l", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="m", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="n", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="o", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="p", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="q", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="r", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="s", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="t", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="u", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="v", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
     Selection.Replace What:="w", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="x", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="y", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Replace What:="z", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("A1").Select
    End Sub

The first bit is selecting the relevant column and pasting into the column next door the second bit is just a find and replace of all the letters of the alphabet.

The way I learned code was by recording a few macros and going back to the code to see how excel interpreted the code, and then reading up once I felt happy with this. There are some interesting threads about learning code on this site as well (People might snort at my method).
Click to expand...


Thank you so much!!! you are a life saver. I just tried it and it work! You're my hero.
 
Upvote 0
Try to use a Word worksheet as helper to remove the English letters as the following :

1] Open a MS Word newsheet

2] Highlight your Excel sheet of range A1:A6 >> mouse right click >> select Copy

3] Go to the opened Word newsheet >> mouse right click >> select Paste >> highlight all datas >> Ctrl+F

Find >> Find what, enter : [^65-^122]

Replace >> Replace with ( keep blank) , Click "More >>" button >> Check the box of " Use Wildcards" >> Click "Replace All" >> Close

4] Highlight and copy the modified datas from Word sheet >> then paste back to the Excel sheet >> Finish
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Two column value reconciliation comparison function.
Replies
2
Views
560
shaokui123
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top