+ Reply to Thread
Results 1 to 10 of 10

Split String into cells

  1. #1
    Registered User
    Join Date
    11-27-2018
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    13

    Split String into cells

    Hi Guys, I have on the "A" row a list of Names with Date and a number telling the action number, like this: "Robert.Smith.2020.1.log"

    I need to:
    extract to "B" row the name like this: "Robert.Smith"
    extract to "C" row the date like this: "2020"
    extract to "D" row the action like this: "1"

    The problem I'm facing is that the name sometimes had 1 dots, and sometimes 2 or 3 dots, example: "Robert.Smith.Jr.2020.1.log" or "Mr.Robert.Smith.Jr.2020.1.log"
    The only constant is that all the strings ends with "9999.9.AAA"

    Any idea of how to start this? I think reading the dots from right to left, but I can't figure out how in VBA
    Thanks in advance

  2. #2
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Split String into cells

    B1: =left(a1,len(a1)-11)
    C1: =left(substitute(a1;b1&".",""),4)
    D1: =mid(right(a1;5),1,1)

  3. #3
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Split String into cells

    Try the below code based on the provided sample ...

    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Split String into cells

    Does this macro do what you want...
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-27-2018
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    13

    Re: Split String into cells

    Hi nankw83, your code works great, I made a mistake on explain.

    Sometimes the Action part contains 2 characters, for example "Robert.Smit.2020.10.log" in this case, the code extract a "." character, another problem is that the Date part some cells had 5 or 6 numbers, like this:
    "Robert.Smit.20201.10.log"

    Is there a way to do it based on "." characters instead of constant values or counts of strings?
    The list is very big and there is a lot of combinations, but the only constant is the info is betwen dots.

    I dont know if this explaination will be more clear:
    Part1.Part2.Part3.DATE.ACTION.ext

    The string starting on the first character and ending on to the "." before DATE need to be in Cell B
    The string DATE allways is between the antepenultimate DOT and the penultimate DOT and need to be in Cell C
    The string ACTION allways is betwen the last DOT and the penultimate DOT and need to be in Cell D

    This strings are diferent size on each customer and log.

    Thanks in advance!

  6. #6
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Split String into cells

    Missed the big yellow banner?
    In addition, there are multiple responses.

    Please Login or Register  to view this content.

  7. #7
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Split String into cells

    Quote Originally Posted by tomasrage View Post
    Sometimes the Action part contains 2 characters
    Just wondering if the code I posted in Message #4 worked for you or not?

  8. #8
    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 String into cells

    An alternative solution is to use Power Query/Get and Transform. Here is the Mcode resulting from the U/I

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    1
    Data.1 Data.2 Data.3.1 Data.3.2
    2
    Mr.Robert.Smith.Jr. 2020 1 log
    3
    Robert.Smith.Jr. 2020 1 log
    4
    Robert.Smith. 2020 1 log
    Sheet: Sheet2

    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced
    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

  9. #9
    Registered User
    Join Date
    11-27-2018
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    13

    Re: Split String into cells

    Yes! it works perfect, I allready add reputation to your code!!

    thanks a lot Rick!!

  10. #10
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Split String into cells

    You are very good at ignoring the other helpers.

+ 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] Split long string to multiple cells
    By bolambert in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-05-2015, 07:55 PM
  2. [SOLVED] Split data string into 2 new cells after 4th occurrence of character
    By syncguy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2015, 10:02 AM
  3. [SOLVED] split a string of text in one cell into 2 new cells after occurance of second comma
    By syncguy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2015, 12:50 AM
  4. [SOLVED] Split a string of data among separate cells
    By rodrignj in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-14-2013, 10:18 AM
  5. [SOLVED] split string into multiple cells
    By sp1dey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2012, 02:03 PM
  6. Split text string into four cells
    By mkvassh in forum Excel General
    Replies: 13
    Last Post: 03-02-2012, 09:16 AM
  7. Split String into Cells with incased values []
    By ezykiwi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2008, 07:52 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