how to extract only character's ???

RAVI P

New Member
Joined
Aug 24, 2019
Messages
1
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi & welcome to MrExcel.
How about


Book1
AB
2john17son1992bornjohnsonborn
3premkumar12from560077premkumarfrom
4king0295of99thekingkingoftheking
End
Cell Formulas
RangeFormula
B2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")
 
Upvote 0
Hi
Running Office 365 on Win 10 :ROFLMAO:

=CONCAT(IF(ISNUMBER(--MID(A1,ROW(1:80),1)),"",MID(A1,ROW(1:80),1)))
 
Upvote 0
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
 
Last edited:
Upvote 0
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)

Code:
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
ABC
1john17son1992bornjohnsonbornjohnsonborn
2premkumar12from560077premkumarfrompremkumarfrom
3king0295of99thekingkingofthekingkingoftheking
4john17#$so%]n1992bornjohnsonbornjohnsonborn
Sheet1
 
Upvote 0
with PowerQuery aka Get&Transform

RawText
A1B2C3D4E5F6G7H8J9K0ABCDEFGHJK
zxc34nbv55qwertyzxcnbvqwerty
AaBb12345CcDdEe678UuRr900AaBbCcDdEeUuRr
B 1 C 2 D 55B C D
john17son1992bornjohnsonborn
premkumar12from560077premkumarfrom
king0295of99thekingkingoftheking

Code:
[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]
 
Last edited:
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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