how to extract only character's ???

[RAVI P]

i have a doubt how to extract only characters from a set of word using only excel

ex

john17son1992born
premkumar12from560077
king0295of99theking

how to get output as
johnsonborn
premkumarfrom
kingoftheking

please clear my doubt

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B
2	john17son1992born	johnsonborn
3	premkumar12from560077	premkumarfrom
4	king0295of99theking	kingoftheking
End

Cell Formulas
Range	Formula
B2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

 

[ISY]

Hi
Running Office 365 on Win 10

=CONCAT(IF(ISNUMBER(--MID(A1,ROW(1:80),1)),"",MID(A1,ROW(1:80),1)))

 

[Fluff]

Or slightly safer (but volatile)
=TEXTJOIN("",TRUE,IF(ISERR(MID(A3,ROW(INDIRECT("1:100")),1)+0),MID(A3,ROW(INDIRECT("1:100")),1),""))

This is an array formula & needs to be confirmed with Ctrl Shift Enter, not just Enter

 

[Peter_SSs]

Welcome to the MrExcel board!

If you only want letters and there could be other characters to exclude (eg &{?) then this one (column B below) might also be of interest. It includes the robustness of copying down and row insertion like Fluff's and is also non-volatile
This is also an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

If you did want to consider a vba solution then this user-defined function allows a much simpler, non-array, formula to be used in the worksheet as shown in column C. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Letters(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^a-zA-Z]"
    Letters = .Replace(s, "")
  End With
End Function

Excel Workbook
	A	B	C
1	john17son1992born	johnsonborn	johnsonborn
2	premkumar12from560077	premkumarfrom	premkumarfrom
3	king0295of99theking	kingoftheking	kingoftheking
4	john17#$so%]n1992born	johnsonborn	johnsonborn
Sheet1

 

[sandy666]

with PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Raw[/td][td=bgcolor:#70AD47]Text[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A1B2C3D4E5F6G7H8J9K0[/td][td=bgcolor:#E2EFDA]ABCDEFGHJK[/td][/tr]

[tr=bgcolor:#FFFFFF][td]zxc34nbv55qwerty[/td][td]zxcnbvqwerty[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AaBb12345CcDdEe678UuRr900[/td][td=bgcolor:#E2EFDA]AaBbCcDdEeUuRr[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B 1 C 2 D 55[/td][td]B C D [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]john17son1992born[/td][td=bgcolor:#E2EFDA]johnsonborn[/td][/tr]

[tr=bgcolor:#FFFFFF][td]premkumar12from560077[/td][td]premkumarfrom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]king0295of99theking[/td][td=bgcolor:#E2EFDA]kingoftheking[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.AddColumn(Source, "Text", each Text.Combine(List.RemoveItems(Text.ToList([Raw]),{"0".."9"})))
in
    Text[/SIZE]

 

[Rick Rothstein]

If you are willing to consider a VBA (a user defined function) like the one Peter posted in Message #5 , then here is a non-RegExp one; but note that I added an optional argument so you can tell the function to retain or eliminate blank spaces that may appear within the text (I do not know if there could ever be a need for such functionality, but it was easy to add into the function, so I did). The default for the optional argument is FALSE meaning space will be removed along with non-letter characters (since this is the default, you can omit the argument or specify it as FALSE)... this will produce same results as Peter's function. If you pass TRUE into the argument, all naturally occurring spaces will remain.

Function Letters(S As String, Optional RetainSpaces As Boolean) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!A-Za-z" & Left(" ", -RetainSpaces) & "]" Then Mid(S, X) = Chr(0)
  Next
  Letters = Replace(S, Chr(0), "")
End Function