How do I take multiple strings within 1 cell and place them in independent cells

Posted on 2014-08-22
Last Modified: 2014-08-29
I have a spreadsheet (see attached) where the cells have multiple values in 1 cell (see tab current).  I want to normalize the information as seen in tab "Need".  Within VBA I would to achieve what is seen in tab "Need".  I assume I need to use the InStr function looking for a space, but I am unsure on how to place the value then in a separate cell ?
Question by:upobDaPlaya
    LVL 11

    Expert Comment

    by:Swapnil Nirmal
    Your need seem to be unclear. think is uploaded wrongly. please check.

    You want this:

    ColA            ColB
    Boy                     "Just another day
                             just tomorrow
                             just in the future"
    Boy                       Just another day
    Boy                      Just another day

    or this:

    ColA      ColB
    Boy               Just another day
    Boy               just tomorrow
    Boy               just in the future
    LVL 11

    Accepted Solution

    If second one is your solution then PFA excel with macro.

    Your results will be reflected in need2 sheet.

    Author Comment

    I think I understand you are doing a split function where a line break is found ?
    LVL 27

    Assisted Solution

    by:Glenn Ray
    @ upobDaPlaya:

    Yes; in line 18, he is splitting the value of the cell using the LineFeed character (vbLf constant) as the delimiter.  "SplitedString" is the resulting array and he then loops through that array to produce the output.


    Author Closing Comment

    Fantastic function !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    Title # Comments Views Activity
    Excel file corrupted. 13 28
    Excel add date to text 3 30
    Excel Max 6 23
    Excel Averageifs 2 2
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now