Results 1 to 7 of 7

Thread: Stripping out data

  1. #1
    New Member
    Join Date
    Mar 2007
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Stripping out data

    I have and column that reads this "8.50 (13.0)".
    I would like to strip out the data into 2 columns and remove the parenthesis from the second one.

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,940
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stripping out data

    A couple of steps in Power Query.
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
        #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column1.2.2"})
    in
        #"Removed Columns"
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


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

    Default Re: Stripping out data

    How about

    ABC
    28.50 (13.0)8.5013.0

    Request



    Worksheet Formulas
    CellFormula
    B2=LEFT(A2,FIND(" ",A2)-1)
    C2=SUBSTITUTE(MID(A2,FIND("(",A2)+1,100),")","")

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

    Running Office 365 on Win 10

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

    Cool Re: Stripping out data

    another way with PowerQuery (Get&Transform)

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Split = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({" ("}, QuoteStyle.Csv)),
        Replace = Table.ReplaceValue(Split,")","",Replacer.ReplaceText,{"Column1.2"}),
        Type = Table.TransformColumnTypes(Replace,{{"Column1.1", type number}, {"Column1.2", type number}})
    in
        Type
    Column1 Column1.1 Column1.2
    8.50 (13.0)
    8.5
    13
    10.44 (11.11)
    10.44
    11.11
    Last edited by sandy666; Jul 17th, 2019 at 08:02 AM.
    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!

  5. #5
    New Member
    Join Date
    Mar 2007
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stripping out data

    Very cool, but this one is over my head.
    Reminds me just how little I know about Excel.
    But thanks and I will follow up on this with some study.

  6. #6
    New Member
    Join Date
    Mar 2007
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stripping out data

    This is more my speed.
    Thank you for replying.

    Kindest regards

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

    Default Re: Stripping out data

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •