Results 1 to 7 of 7

Thread: how to extract only character's ???
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation how to extract only character's ???

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,383
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: how to extract only character's ???

    Hi & welcome to MrExcel.
    How about

    AB
    2john17son1992bornjohnsonborn
    3premkumar12from560077premkumarfrom
    4king0295of99thekingkingoftheking

    End



    Worksheet Formulas
    CellFormula
    B2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Nov 2009
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to extract only character's ???

    Hi
    Running Office 365 on Win 10

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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,383
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: how to extract only character's ???

    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 by Fluff; Aug 24th, 2019 at 04:51 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,005
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: how to extract only character's ???

    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
    Sheet1

    ABC
    1john17son1992bornjohnsonbornjohnsonborn
    2premkumar12from560077premkumarfrompremkumarfrom
    3king0295of99thekingkingofthekingkingoftheking
    4john17#$so%]n1992bornjohnsonbornjohnsonborn

    Spreadsheet Formulas
    CellFormula
    B1{=CONCAT(IF(ABS(77.5-CODE(MID(UPPER(A1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)))<13,MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1),""))}
    C1=Letters(A1)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,871
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: how to extract only character's ???

    with PowerQuery aka Get&Transform

    Raw Text
    A1B2C3D4E5F6G7H8J9K0 ABCDEFGHJK
    zxc34nbv55qwerty zxcnbvqwerty
    AaBb12345CcDdEe678UuRr900 AaBbCcDdEeUuRr
    B 1 C 2 D 55 B C D
    john17son1992born johnsonborn
    premkumar12from560077 premkumarfrom
    king0295of99theking kingoftheking


    Code:
    // 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
    Last edited by sandy666; Aug 24th, 2019 at 09:53 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,252
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: how to extract only character's ???

    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •