+ Reply to Thread
Results 1 to 23 of 23

split data for multiple digits & letters , symbols into sheet based on another

  1. #1
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    split data for multiple digits & letters , symbols into sheet based on another

    hi
    I want to split data into sheet master based on sheet main . there are many different digits & letters , symbols . so it should split column B into multiple columns B,C,D in sheet master based on sheet main . I put the expected result from column E: G . always I have structure of data like in sheet MAIN and the real data about 700 rows .
    sorry if the detailes are poor , just tell me if anybody interest .
    Attached Files Attached Files
    Last edited by Alaa-A; 01-02-2022 at 05:57 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: split data for multiple digits & letters , symbols into sheet based on another

    VBA or formula?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data for multiple digits & letters , symbols into sheet based on another

    thanks for your interesting. I wish achieving by vba

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: split data for multiple digits & letters , symbols into sheet based on another

    VBA code
    Output is in columns K:M

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 01-02-2022 at 09:36 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: split data for multiple digits & letters , symbols into sheet based on another

    Another Option...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: split data for multiple digits & letters , symbols into sheet based on another

    Here is an alternative means by using Power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  7. #7
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data for multiple digits & letters , symbols into sheet based on another

    thanks guys ! but I would the result from COLUMN B:D based on OP
    so it should split column B into multiple columns B,C,D in sheet master based on sheet main
    I put the expected result from column E: G just to understand what I want

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: split data for multiple digits & letters , symbols into sheet based on another

    Revised code
    Please Login or Register  to view this content.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: split data for multiple digits & letters , symbols into sheet based on another

    PHP Code: 
    Option Explicit
    Sub test
    ()
    Dim i&, cell As Range
    With Sheets
    ("Master")
        For 
    2 To Sheets("Master").Cells(Rows.Count"B").End(xlUp).Row
            
    For Each cell In Sheets("Main").Range("B2:B" Sheets("Main").Cells(Rows.Count"B").End(xlUp).Row)
                If 
    Replace(cell cell.Offset(, 1) & cell.Offset(, 2), " """Like Replace(.Cells(i"B"), " """Then
                    
    .Cells(i"B").Value cell.Value
                    
    .Cells(i"C").Value cell.Offset(, 1).Value
                    
    .Cells(i"D").Value cell.Offset(, 2).Value
                End 
    If
            
    Next
        Next
    End With
    End Sub 
    Quang PT

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: split data for multiple digits & letters , symbols into sheet based on another

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data for multiple digits & letters , symbols into sheet based on another

    @sintek thanks but if i run repeatedly will shows error subscript out of range
    HTML Code: 
    may you fix it by making it replace data every time run the macro,please?

  12. #12
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data for multiple digits & letters , symbols into sheet based on another

    @kvsrinivasamurthy the same problem as I issued in post#11 , but the error in this line
    HTML Code: 

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: split data for multiple digits & letters , symbols into sheet based on another

    What you mean run repeatedly...
    As per your request...The code takes your existing data and splits it into 3 columns...So there is no more original data...
    Have no idea why you refer to Main Sheet all the time as this is the exact result you require in Master Sheet
    Currently Master Sheet has the exact same data as Main Sheet...Only the 3 columns is combined into 1 column...
    Or am I missing something...

    Perhaps you should explain your requirement in more detail...
    Last edited by sintek; 01-03-2022 at 03:58 AM.

  14. #14
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data for multiple digits & letters , symbols into sheet based on another

    What you mean run repeatedly...
    that meant I will add new data in sheet main then should macro run normally every time add new data without error , but it seems the code work just one time . it doesn't work for a new data after has already run the macro .

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: split data for multiple digits & letters , symbols into sheet based on another

    But your Main Sheet is the exact same setup as per what you require in Master Sheet...

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: split data for multiple digits & letters , symbols into sheet based on another

    Try this code
    Please Login or Register  to view this content.

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: split data for multiple digits & letters , symbols into sheet based on another

    Gonna go around in circles all day long if we do not get a clearer explanation...
    I think all of us have the same understanding...Which is not OP understanding...

  18. #18
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data for multiple digits & letters , symbols into sheet based on another

    @sintek ,kvsrinivasamurthy ,bebo021999 my apologies ! I misunderstood . you're right
    thanks very much

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: split data for multiple digits & letters , symbols into sheet based on another

    my apologies ! I misunderstood . you're right
    What does this mean...What is it you actually wanted to achieve...You have not answered any of my previous posts...I still am confused...

  20. #20
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: split data for multiple digits & letters , symbols into sheet based on another

    Quote Originally Posted by Alaa-A View Post
    @sintek ,kvsrinivasamurthy ,bebo021999 my apologies ! I misunderstood . you're right
    thanks very much
    Surely we outnumber you. LOL.
    anyway, nice to hear it solved.

  21. #21
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data for multiple digits & letters , symbols into sheet based on another

    What does this mean...What is it you actually wanted to achieve...You have not answered any of my previous posts...I still am confused...
    every thing is ok based on OP just what I meant somtimes run the macro repeatedly inaccedintly then it will shows the rror because the data have already splited ,then macro can't split data are divided again . what I looked for to avoid the error when run the macro repeatedly .

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

    Re: split data for multiple digits & letters , symbols into sheet based on another

    Quote Originally Posted by Alaa-A View Post
    what I meant somtimes run the macro repeatedly inaccedintly then it will shows the rror because the data have already splited ,then macro can't split data are divided again . what I looked for to avoid the error when run the macro repeatedly .
    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data for multiple digits & letters , symbols into sheet based on another

    @jindon fantastic ! this is exactly what I want .
    much appreciated
    Last edited by Alaa-A; 01-03-2022 at 05:22 AM.

+ 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. Replies: 4
    Last Post: 06-16-2021, 11:47 AM
  2. [SOLVED] Conditionally format based on a formula matching a string with digits, not letters
    By MattDay2u in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 10-20-2017, 11:04 AM
  3. Split based on letters and numbers
    By mma3824 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-06-2015, 03:58 PM
  4. Split Data Into Multiple Workbook Based On Column value with exisitng sheet
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2013, 09:23 AM
  5. Split cells based on Capital Letters
    By AntyLani in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2013, 11:24 AM
  6. [SOLVED] extracting digits only from column with letters and digits
    By Jayne in forum Excel General
    Replies: 11
    Last Post: 08-20-2012, 10:38 PM
  7. Replace all symbols/letters according to a list in 2nd. sheet
    By wali in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2009, 04:07 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