Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: How to remove few texts from a cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to remove few texts from a cell

    Hi All,

    How to remove few texts from a cell and list down the values in separate cells

    Example: 1 of 100 1 of 200 1 of 500

    I want to remove the "1 of" from the cell and list down the values in horizontal position one by one as

    A2 = 100
    A3 = 200
    A4 = 500

    Can anyone please help.

    Thanks

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

    Cool Re: How to remove few texts from a cell

    with Power Query

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Split = Table.SelectRows(Table.TransformColumnTypes(Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("1 of ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),{{"Column1", Int64.Type}}), each ([Column1] <> null))
    in
        Split
    Column1 Column1
    1 of 100 1 of 200 1 of 500
    100
    200
    500
    Last edited by sandy666; Sep 4th, 2019 at 08:17 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
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Apr 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove few texts from a cell

    Thank you,
    But where do we need to paste this code, in a module or in a sheet.
    I pasted in a module, but it shows in red.

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

    Default Re: How to remove few texts from a cell

    how about, with a formula

    AB
    21 of 100 1 of 200 1 of 500100
    3200
    4500

    End



    Worksheet Formulas
    CellFormula
    B2=TRIM(MID(SUBSTITUTE($A$2,"1 of", REPT(" ",100)),ROW($A1)*100,100))+0

    Last edited by Fluff; Sep 4th, 2019 at 08:38 AM.
    - 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
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,800
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: How to remove few texts from a cell

    Quote Originally Posted by Feroz90 View Post
    Thank you,
    But where do we need to paste this code, in a module or in a sheet.
    I pasted in a module, but it shows in red.
    you didn't read first line of the post, this is not vba but M-code for PowerQuery

    What is Power Query?
    Last edited by sandy666; Sep 4th, 2019 at 09:07 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
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    Board Regular
    Join Date
    Apr 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove few texts from a cell

    Thank you both, If it is possible with VBA code, it will be helpful.

    And I don't know why, the formula isn't working for me.

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

    Default Re: How to remove few texts from a cell

    Are you just going to be looking at one cell, or multiple cells?
    If multiple cells, where are they & how do you want the result displayed?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Apr 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove few texts from a cell

    As of now, I am ok with one cell, I just wanted to split the texts and update the values in a list.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,994
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: How to remove few texts from a cell

    How about
    Code:
    Sub feroz90()
       Dim Sp As Variant
       Sp = Split(Replace(Range("A2").Value, "1 of ", ""))
       Range("B2").Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Apr 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove few texts from a cell

    Thank you Fluff, but i am getting the number with one cell below, like 300 in B2 & 100 in B4.

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
  •