+ Reply to Thread
Results 1 to 25 of 25

Split string variable by specific pattern

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Split string variable by specific pattern

    Hello everyone
    I have a string variable (from a text file) which I need to split by specific pattern ..
    The pattern would be space followed by numeric values then the symbol ) like that
    Please Login or Register  to view this content.
    So how can I split by this pattern
    Example:
    input = 1) any data 2) some other data 3)other data 4)and so on ...
    Output would be
    1) any data
    2) some other data
    3)other data
    4)and so on ...

    Thanks advanced for help
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    Hi Yasser, try this code :
    Please Login or Register  to view this content.
    Attached Files Attached Files
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Thank you very much my friend. You are awesome and brilliant
    Best and Kind Regards

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    You are welcome, glad I can help.

    Regards

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Thanks a lot
    I have posted similar thread here to import from text files ..
    http://www.eileenslounge.com/viewtopic.php?f=30&t=29716

    It is working well and I can import from text files .. BUT
    There is a problem when trying to my text files which are in Arabic

    Any idea how to fix that?

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Here are the files (one is OK which in ANSI and the other which UTF-8 is not OK)
    and here's the code
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    Please Login or Register  to view this content.
    Last edited by karedog; 04-14-2018 at 03:35 AM.

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    That's wonderful ..
    Now the text file "Problem Text File.txt" solved and the other ANSI text file has the problem .. It seems that there must be a switch but I don't know how to check ANSI and UTF-8 ?

  9. #9
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    I have changed the code on post #7.

    Then you must change this line from Sub Loop_Through_Text_Files() :
    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Thank you very much for this wonderful UDF. That solved the problem of UTF-8 and ANSI point
    Thanks a lot my friend

    One last point and I promise not to ask more here ..
    Have a look at the following text file ... for number 3) :: there are multiple lines .. How during the import of the text file keep those lines (exactly as using Chr(10) in VBA)

  11. #11
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    Change to :
    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Thank you very much
    Now there's an empty row after the title of the text file .. It didn't exist before ..How can I get rid of it? I tried but I didn't succeed ..

  13. #13
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    This should do everything :
    Please Login or Register  to view this content.
    Last edited by karedog; 04-14-2018 at 10:26 AM.

  14. #14
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    You are AMAZING. Thank you very much my friend for great and incredible help
    Best and Kind Regards

  15. #15
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    You are welcome, glad to help.

    Regards

  16. #16
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Hello again
    How to edit this pattern
    Please Login or Register  to view this content.
    To allow using bracket ) or hyphen - or asterisk * .. Any of these three symbols may be followed by space and may be not followed by space

  17. #17
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    Should be :
    Please Login or Register  to view this content.

  18. #18
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Thanks a lot my friend
    I am trying to learn how to build a pattern
    Here's a pattern of one of the files
    Please Login or Register  to view this content.
    It is working well .. but how to exclude empty lines from results?

  19. #19
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    I don't understand what you asking. Is this a new case (different data file and pattern from post #1 - #17) ?

  20. #20
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Yes this is a different pattern .. I could play with it because of you .. I have learned a lot from you
    Just I noticed empty lines and if there is an easy way to exclude them but if not, don't worry about it

  21. #21
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    Even if this could be done using single pattern, it could be not an easy one, would be better done using 2 different patterns.
    Can you post a sample data, before and after ?

  22. #22
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Here's simple sample to illustrate the issue
    Untitled.png
    the red rectangles are empty lines which are not desired
    Attached Files Attached Files

  23. #23
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    I attach excel and the data files, included previous files
    Attached Files Attached Files

  24. #24
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Split string variable by specific pattern

    Believe me .. You're amazing and brilliant
    Thank you very very very much. Thanks a lot for all great help
    Have a nice time my friend

  25. #25
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Split string variable by specific pattern

    Thank you, glad to help.

    Regards

+ 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] How to check if a cell contains a specific string from a variable?
    By RustyNails in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-11-2015, 02:15 AM
  2. [SOLVED] Pull specific string of data from variable cell value
    By Speshul in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2014, 01:26 PM
  3. Replies: 4
    Last Post: 06-04-2014, 04:12 PM
  4. Replies: 5
    Last Post: 04-06-2014, 08:59 PM
  5. [SOLVED] Split String in 3 Blocks of #s, Extract each Block to copy on specific Cell
    By david gonzalez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2013, 12:14 AM
  6. search for specific text pattern within string
    By nzdzy2 in forum Excel General
    Replies: 3
    Last Post: 10-10-2011, 11:47 AM
  7. [SOLVED] How do I find a specific characters position in a string variable
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2006, 10:30 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