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

Posted on 2014-08-22
Medium Priority
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
  • 2
  • 2
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 40280269
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

Swapnil Nirmal earned 1800 total points
ID: 40280365
If second one is your solution then PFA excel with macro.

Your results will be reflected in need2 sheet.

Author Comment

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

Assisted Solution

by:Glenn Ray
Glenn Ray earned 200 total points
ID: 40283729
@ 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

ID: 40294042
Fantastic function !

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

840 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