+ Reply to Thread
Results 1 to 35 of 35

Splitting strings into next row

  1. #1
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Splitting strings into next row

    Hi all,

    I'm trying to split the strings in column S into the next row like what it shown in the attachment.

    In column S sometime there are two or more barcodes what I want to achieve is, if there's more than one barcodes split them into the row after.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    Try
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @Jindon,

    Is there anyway to keep the same format as it shown on the after sheet?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    Then try this one.
    Please Login or Register  to view this content.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Splitting strings into next row

    Hi,

    Here's one way with the sheet te active sheet

    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @jindon

    Thanks for the help it works just how I wanted.

    Another question is it possible to also do the same in column G to split between the colours and match back with the barcodes?

  7. #7
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @Richard Buttrey

    Thanks for the help Richard, it does how I wanted as well.

    Like with what I asked Jindon is it possible possible to also do the same in column G to split between the colours and match back with the barcodes?

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    How do you want to match up?

    If you upload a workbook with before/after will help.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    See if this is how you wanted.
    Please Login or Register  to view this content.
    Edit: Code
    Last edited by jindon; 12-13-2018 at 10:53 AM. Reason: Code has been replaced

  10. #10
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @Jindon

    Thanks it works on the example I upload, but when I try it on the main worksheet it has an error showing up, please see attachment thanks
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    OK, try this one.
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @Jindon

    Thank you very much for the help it works perfectly!

  13. #13
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @Jindon

    I just realised that the colour matches but in the original it didn't deleted the other colour.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    OK, I'll look at it tomorrow.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    You have typo in row 120,
    COBLAT =146:C-151:UK10=33,UK14=6;C-152:UK6=24,UK16=9;C-153:UK12=24,UK14=11;C-154:UK8=30,UK4=9

    COLBALT - CLZ8983
    BURGUNDY - CLZ8984

    Col.G remains the same, if type, no match found for the display purpose.
    Test this hard.
    Please Login or Register  to view this content.
    Last edited by jindon; 12-13-2018 at 11:55 PM.

  16. #16
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @jindon

    Yea I understand that's fine if it only have one colour. What I mean was like row113

    STONE=141:U-47:UK10=27,UK14=12,UK4=9;U-48:UK8=42;U-49:UK6=27,UK2=12,UK12=12;KHAKI=156:U-50:UK8=45;U-51:UK2=14,UK6=29,UK12=14;U-52:UK4=11,UK10=29,UK14=14

    When I ran your code it matches the colours but the original still have two colours in col.G like below.

    KHAKI-CMA1427 matches with only KHAKI=156:U-50:UK8=45;U-51:UK2=14,UK6=29,UK12=14;U-52:UK4=11,UK10=29,UK14=14

    But STONE-CMA1428 still showing two colours STONE=141:U-47:UK10=27,UK14=12,UK4=9;U-48:UK8=42;U-49:UK6=27,UK2=12,UK12=12;KHAKI=156:U-50:UK8=45;U-51:UK2=14,UK6=29,UK12=14;U-52:UK4=11,UK10=29,UK14=14

    Is it possible to remove the other colour?

    Thanks

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    Try replace "Test" sub with the following code and see how it goes.
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @jindon,

    It still not working properly, after running the code some works and some doesn't.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    It seems to be working for me.

    If you can point out where do you think it is not working, it would help.

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    This will color the cell in Col.S where the row has problem.
    Again when the row has problem Col.G will have original data.
    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @jindon

    I have attached the example with the code ran and highlight the rows in yellow with problems.

    Thanks
    Attached Files Attached Files

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    It seems working correctly to me.

    Nee your explanation why and your desired result.

  23. #23
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @Jindon

    Apologised for the late reply, I was away during the weekend. In the Example 2 workbook I'd attached, the ones highlight in yellow matches with the colours. This is what I wanted, but for example in row124 this matches the colour 'Stone'. However, there's the other colour 'Khaki' in the same cell as 'Stone'. What I desired is that each colour in column G matches the same colour in column S with only the colour it matches. If there's more than one colour after the split I want that colour to be remove.

    I'm not sure if this only apply on my side but when I ran your code this is what it shown.

    Thanks for the help.

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    Can you just upload a workbook ONLY with the rows that are not resulting as you expect and EXACT result that you want in different sheet?

  25. #25
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    Ah ok Please see below attachment and thanks.
    Attached Files Attached Files

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    Try this one and see...
    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @Jindon

    After ran the code there seem to had more problems please see attachment.
    Attached Files Attached Files

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    You are not using the one I last posted.
    Please Login or Register  to view this content.

  29. #29
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @jindon

    Apologised, I thought it was like before only replace the test sub. This works how I wanted.

    Thanks for taking your time in the last few days helping me, I apologised I couldn't explain very well how I wanted and it took so much of your time, very appreciated for what you have done.

  30. #30
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    You are welcome.

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

  31. #31
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @jindon

    I'm not sure if you still remember this but I have new data witch doesn't seem to work with the code you gave me.

    The problem is with column g it deletes some strings after the code ran.
    Attached Files Attached Files

  32. #32
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    I'm very busy until tomorrow, so I will look at it maybe in 2 or 3 days after.

  33. #33
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    How is it not working?

  34. #34
    Forum Contributor
    Join Date
    03-02-2018
    Location
    england
    MS-Off Ver
    2016
    Posts
    156

    Re: Splitting strings into next row

    @jindon

    As you can see from the workbook that I attached. For example G19 before and after there were data missing after the code ran.

    Before was

    BLACK =600:P74:S=110;P75:S=140;P76:S=120;P77:S=140;P78:L=90;

    After

    BLACK =600:P74:

  35. #35
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Splitting strings into next row

    Try change the pattern in "Function GetColor"
    Please Login or Register  to view this content.

+ 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. [SOLVED] Splitting strings if a character is present within them
    By mfd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2014, 04:25 PM
  2. Splitting Long Strings
    By moericus in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-07-2008, 04:09 PM
  3. Splitting Strings
    By jay_hl123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-23-2007, 01:50 PM
  4. String splitting for inconsistent strings
    By Richard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-21-2006, 11:00 PM
  5. Splitting Text strings in one cell
    By Wardy1 in forum Excel General
    Replies: 1
    Last Post: 05-18-2006, 08:10 AM
  6. splitting strings
    By tad_wegner in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-27-2006, 12:10 PM
  7. splitting number strings in excel
    By Bob in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2005, 12:06 AM

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