+ Reply to Thread
Results 1 to 31 of 31

Extract every words, after certain character, in any position within a cell. Then split

  1. #1
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Extract every words, after certain character, in any position within a cell. Then split

    Need help to


    1. Extract every words, after "Type>", in any position within a cell.
    2. Split the results into 2 columns, after cross checked with existing list

    Hope these could be solved in 1 step.
    Please see attachment.


    Thank you
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Where have these come from?

    Excel 2016 (Windows) 32 bit
    B
    19
    ****Top
    20
    ****Bottom
    21
    ****Outerwear
    22
    ****Sets
    23
    ****Shoes
    Sheet: Sheet1
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    So until you answer the question, PQ can get us this far:

    Excel 2016 (Windows) 32 bit
    D
    13
    Types
    14
    Christmas, Festive, Outerwear
    15
    Chinese New Year, Sets
    16
    Shoes
    Sheet: Sheet1

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    the list is a cross reference table.
    i haven't created it yet, but it would be around 20 to 30 data within the list

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,484

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    crossposted: https://www.mrexcel.com/board/thread...split.1139217/

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Hello, thank you for information & i'm really sorry for the damage i've made.

    -------------------------------------------------

    already wrote there, is:

    i'm sorry for the damage i've made.
    this is the link to this excel problem & what kind of progress has been made so far

    the link...


    thank you

    -------------------------

    by the way, i tried to paste the link above,
    but when press post quick reply, it said i couldn't put any link,
    so i remove the link

    thank you

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    OK - I was working on this for you, but as you will no doubt have got a solution elsewhere by now, I'll leave it now. Please don't forget to share the solution here. Thanks.

  8. #8
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    AliGW,
    thank you for your response,

    i already read your first solution & reply about table you asked.

    actually i don't know how to apply your solution in excel, (sorry i'm newbie).
    so i'm googling how to apply it.
    i will let you know if i already able to apply it

    by the way, please don't get offended,
    i really appreciate your help &
    didn't aware at all about cross posting.
    but now i knew & will be careful.

    i am sorry, AliGW.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    It's fine to cross-post a long as you share that with us. However, if I know you are getting help elsewhere, I tend to prioritise other queries. Hope this makes sense.

  10. #10
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    will it be ok if i'm hope the help come from you?

    already try your solution:
    1. i open the excel which i sent to you
    2. go to Data > new query > from other sources > blank query
    3. paste your code
    4. this is shown
    Expression.Error: We couldn't find an Excel table named 'Table1'.
    Details:
    Table1

    which step did i miss?


    thank you

  11. #11
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Try the attached file. Just click the "Process Data" button.
    Attached Files Attached Files
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  12. #12
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Mumps1
    Thank you for your feedback


    1) i tried it & already 90% on target

    current solution:
    1st column | 2nd column
    Outerwear | Christmas, Festive, Outerwear
    Sets | Chinese New Year, Sets
    Shoes | -

    expected solution:
    1st column | 2nd column
    Outerwear | Christmas, Festive
    Sets | Chinese New Year
    Shoes | -



    2) if this able to solve, am i allowed to edit your solution to meet real condition?
    and if allowed, do you have guide/step by step to edit the code?


    Thank You

  13. #13
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    The macro I suggested works on the file you posted as you requested. If your actual file is different, attach a copy of that file and explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

  14. #14
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    about why the solution is 90%, because

    outerwear & sets should not appear in 2nd column
    because they already exist in 1st column

    (please see shoes, which only shown in 1st column, and not shown in 2nd column)

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    The PQ solution attached involves loading the first table and then merging it with the category table.

    If you want full instructions then let me know.
    Attached Files Attached Files

  16. #16
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Try:
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Hello AliGW,


    yes it works,
    now i will try to process the real data


    Thank you

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Yes, it works.

    If you need more help, just shout. I will be around again tomorrow.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  19. #19
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Mumps1

    thank you for your code,
    however i don't understand how to use it,

    and if it's a macro/VBA, i'm so sorry because i don't know anything about macro at all.

  20. #20
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Hello, AliGW,


    yesterday i tried to adjust your excel layout, to meet the demand which is 5800 products, and i failed.

    what i already did:
    # moving cross reference table to another column, so i could add items to list of data until 5800 product, & also add items to cross reference table, is not working.

    # try to reuse the query thru power query, but my excel 2019 said the queries in this workbook might be incompatible with your current version of excel. These queries were authored with a newer version of excel or power query and might not work in your current version.
    and when i pushed it, there's an error
    is not working also.


    i attached the real excel (content until line 5800)


    thank youAttachment 685599
    Attached Files Attached Files
    Last edited by superjoejoe2000; 07-07-2020 at 02:53 AM. Reason: forget to attach image, & already able to attach excel so i attach excel, not image

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    The tables have to be loaded first.

    If you want me to look at your new scenario, you will need to post a new sample workbook (no need for links to external sites).

    Pasting data post query isn't a problem - you just need to check the query settings.

    Anyway, easier to advise if you provide more realistic sample data.

  22. #22
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Thank you for your response,
    already put real excel file in my 1:42 PM post

    i'd like to re-attach again here, but can not found button manage attachment,
    so i will edit this post in order to make that button appear
    Attached Files Attached Files
    Last edited by superjoejoe2000; 07-07-2020 at 02:58 AM. Reason: cant find button manage attachment thru normal quick reply, so i edit this post to make the button appear

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    You had not attached it when I replied - you did that at 01:53, so after I'd posted.

    I will have a look. I'll get back to you shortly.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    I think this is the best we can do given the anomalies that exist in some of your data. I added a remove duplicates step to the main query.
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Hi AliGW,
    actually need the results in duplicated & without sorted until line 5800


    i tried to:
    excel > query tools > query > edit > applied steps > 2 most bottom:
    sorted rows
    removed duplicates
    press X (to delete)

    & get this error:
    expression.Error: 5 arguments were passed to a function which expects between 2 and 4.
    Details:
    Pattern=
    Arguments=[List]

    then click button > go to error > it brings me to Applied steps > 2nd line > Added index:
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),


    which part do i have to edit in order to eliminate this error?


    Thank you

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    It's impossible to help without the workbook you have manipulated.

    Here's a version with all duplicate rows left in.
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    there are 2 responses towards most recent excel:

    1. the excel is already 99% on target,
    eg: cell B 88 does not contain "type>"
    so the query does not processed this cell,
    query skipped line 89 also,
    and query continues to process cell B 90 which contain "type>"

    the problem:
    the result is not appear in F 90 & G 90, but in F 88 & G 88

    and every B cell without ">Type" would be skipped,
    so the current result only appear until cell F 5325 & G 5325 (and the data is not inline)


    2. i have wild guess about what missed in current query.
    i think there is 1 or more table,
    which had been erased/renamed within query steps
    (maybe this for "clean/minimalist excel layout").

    but this removal/rename cause the excel is not reusable,
    eg:
    i try to fix and add ">type" into cell which doesn't have it before,
    & paste into column B 6 to B 5803,
    but the result doesn't change

    is it possible to:
    adjust existing query,
    to become keep all temporary table & avoid renaming,
    so the excel could be edited & reuse?

    it's really ok if the sheet would be cluttered & not minimalist,
    but at least i could fix the wrong data & maybe apply simple query modification,
    so i could avoid ask the same topic repetitively

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    Sorry - you have now shifted the goalposts, so I'm going to back out at this point.

    I suggest you have a play, but without the type identifier, it's going to be very hard to get what you want.

    What I have given you is a bare bones mock-up of how this could work - you have to adapt it to your real set-up. By doing this, you will learn how it works (which you are going to need to do so you can manipulate it in future).

    Sorry - I have run out of steam on this one.
    Last edited by AliGW; 07-07-2020 at 05:04 AM.

  29. #29
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    it's ok. AliGW

    thank you so much for your help.
    it really helpful

  30. #30
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    No worries. I hope you are able to adapt it.

    I think you will need to hope someone has a VBA solution for the new criteria. Good luck.

  31. #31
    Registered User
    Join Date
    07-02-2020
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    26

    Re: Extract every words, after certain character, in any position within a cell. Then spli

    What I have given you is a bare bones mock-up of how this could work - you have to adapt it to your real set-up. By doing this, you will learn how it works (which you are going to need to do so you can manipulate it in future).


    Yes, i agree,
    last night inch by inch i started to understand how power query works.


    Thank you, AliGW.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find the Position of a Character in Excel Cell
    By elaph in forum Excel General
    Replies: 4
    Last Post: 03-21-2017, 02:50 AM
  2. [SOLVED] Split sentence by specific position not character
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-02-2017, 01:00 PM
  3. Replies: 3
    Last Post: 10-21-2016, 07:46 PM
  4. Find position of designated character in Cell
    By CAABYYC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2016, 05:50 PM
  5. [SOLVED] find the character position in a string of the last occurrence of a nominated character
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2014, 11:52 PM
  6. Find Position of a Character in a Cell
    By daviieejay in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-30-2013, 11:08 PM
  7. Replies: 2
    Last Post: 08-06-2013, 08:49 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1