473,623 Members | 2,453 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Separating data in an Excel cell into multiple cells

2 New Member
Hi all,
I have no clue how to slove this problem & am desperate for an answer:

I'm creating a spreadsheet and need to now how to seperate data within a single cell into multiple cells.

The info is currently stored in a single cell using line breaks and I want to seperate the info in each line break into specific cells.

Example:

red
blue
green

(above is in one cell with line breaks) into:


red

blue

green

(above is transformed into three different cells).


If anyone can help that would be great, very very much appreciated!!
Josh
Sep 24 '07 #1
5 5814
yashgvt
4 New Member
Hi all,
I have no clue how to slove this problem & am desperate for an answer:

I'm creating a spreadsheet and need to now how to seperate data within a single cell into multiple cells.

The info is currently stored in a single cell using line breaks and I want to seperate the info in each line break into specific cells.

Example:

red
blue
green

(above is in one cell with line breaks) into:


red

blue

green

(above is transformed into three different cells).


If anyone can help that would be great, very very much appreciated!!
Josh
Hi Josh,

AFAIK there is no function or procedure to do this, however if the data is large and is in one cell, then copy the contents of cell and paste it in a word document where you can convert text to table. Then copy the table and paste it in excel.

I know this is not exactly what you expected. just i am sharing my thoughts.

Regards,
Sep 24 '07 #2
jbedwe02
2 New Member
Thanks for the info Yashgvt, its a toughy and I didnt think there was a nice easy way to do this. The problem is I have heaps of cells that need doing and I cant spend the time doing them manually.
If there is no exact way I might go back to the start and work out a way not to have to use line breaks and instead just use whole cells for differing points of info.

Is there anyway we can suggest to Microsoft that for their next edition can they include this function?!?! lol, im that desperate...

Cheers
Sep 24 '07 #3
pbmods
5,821 Recognized Expert Expert
Changed thread title to better describe the problem.
Sep 24 '07 #4
jjsmith
2 New Member
Hi,

Theres two ways you can do this, either make complex formulas as described here:

http://www.informit.co m/articles/article.aspx?p= 174320&seqNum=3

OR...

Use the Excel Wizard (in Excel 2003 its called Text to Columns under the Data menu) as described here:

PS: There's no need to ask Microsoft to include such a feature as its apparently been there years, but maybe they could either rename it to something more descriptive or list it in their help file as I wasted ages trying to use method 1 before stumbling across method 2!! ;)

JJ
Nov 8 '07 #5
jjsmith
2 New Member
whoops forgot to link to where I found out about method two..

http://www.uwec.edu/help/Excel03/ws-split.htm

Thanks UWEC :)

Note:
I found this info in google searching for "separating data in Excel cell" which nearly matches your post heading exactly, which is also how I noticed your post and felt sorry for you as I myself have just spent ages trying to figure this out!!!
Nov 8 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

9
8888
by: Julie Miles | last post by:
I need to pull several tables of data from Excel into a web page, but when I use Excel's "Save as web page" function, I get an enormous file containing a massive amount of css formatting. I'd like to strip out this css so that I can apply my own style sheet; does anyone know whether there is a way to do this, other than manually? Either a way to export the data from Excel that doesn't include the css formatting, or a utility that will...
0
19861
by: Carlos Lozano | last post by:
Hi, I have an application that creates Excel files and I ma having trouble formatting cells. I fill all cells required, sewt fonts and everything, but have a cell that contains multiple text rows. ie. Company one Address abc
2
46470
by: Chris Bellini | last post by:
Greetings! I'm developing a C# application that needs to read some data from a selected XLS file. I've used VB in the past to automate Excel but this is the first time I've used C#. Back in VB, I was able to retrieve a cell's contents via something like this: Set objExcellApp = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open(strPathToXLS) ' retrive the contents of A1 strData = objExcel.Cells(1, 1).Value
1
3134
by: Grey | last post by:
I have created a asp.net form for user to input data. After input the data, user need to click a button to export the input data to excel for data analysis with excel pivot table function. is it possible to do that?? if so, any sample for me as i am a new in designing pivot table with asp.net million thanks
2
7647
by: Lenonardo | last post by:
Hi. I'm writing a VB.Net application to update multiple Excel Worksheets. I'm using late binding (i.e. all variables are objects + use createobject) I develop the application on an XP machine with Excel 2002 (version 10.0) installed - and the code works fine. I then test the code on a laptop running Excel 2000 (version 9.0) and it
1
5840
by: Glen Vermeylen | last post by:
Hi, For a project at school we have to automate the assignment of seats in classrooms to students during the exams. The lady who previously did everything manually kept the layouts of the classrooms in an excel-document: 1 sheet per classroom, and she marked the cells which represent the seats with a border. The problem however is that she sometimes merged cells together to get a better layout.
6
4058
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is this possible? If so, I really need the code immediately. Thank you, Elena
2
1872
by: iheartvba | last post by:
Hi, I have got text in excel which I want separated into 2 cells, there is one consistency within this text, the text I want to keep in the first cell ends with a colon ":" the text I want in the second cell starts with a Dollar Sign "$". here is an example of the text (the text I want separated into the second cell is bold) : Total reportable fringe benefits amounts: $1,000 So in summary I want to to Take all the text after the colon...
1
6965
by: raul15791 | last post by:
Hi, I'm new to C#. I'm writing a program that open a new excel file, write into it and lastly close the file. But the program is that there will be a orphaned process named EXCEL.EXE left on the process queue whenever the program quit. Anyway to terminate it automatically when the application exit? I read some previous postings on the same problem and try to use Marshal.ReleaseCOMObject and Garbage Collection. None of these works. Here's my...
0
8227
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8670
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8613
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6106
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5561
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4074
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4164
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2602
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
1473
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.