Text-to-Column with Paragraph Delimiter

Hi all --

I have an Excel sheet where one column has entries on two lines in the
single cell (separated by a paragraph mark).  I want to split the
column into two using the paragraph mark as the delimiter -- but I
haven't been able to do this successfully.  Is it possible and, if so,
how do I go about it?

Many thanks,

AL
0
lombarap (2)
7/28/2009 3:00:47 PM
excel 39879 articles. 2 followers. Follow

1 Replies
500 Views

Similar Articles

[PageSpeed] 37

Don't exactly know what you mean by a "paragraph mark",
but if you used Char(10) (<Alt> <Enter),
try this is TTC:

Use "Delimited" and in the second step of the wizard,
click on "other" and enter
<Ctrl> <J>
(Of course, you realize that nothing will be visible in the box.)

Then <Finish>
-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"AL" <lombarap@gmail.com> wrote in message 
news:327d43bc-5365-4bd8-a9e7-34e129f6ff90@g1g2000pra.googlegroups.com...
Hi all --

I have an Excel sheet where one column has entries on two lines in the
single cell (separated by a paragraph mark).  I want to split the
column into two using the paragraph mark as the delimiter -- but I
haven't been able to do this successfully.  Is it possible and, if so,
how do I go about it?

Many thanks,

AL 


0
ragdyer1 (4060)
7/28/2009 3:55:15 PM
Reply:

Similar Artilces:

How to give a string of data into different columns
Super expert, If I have cell containing a series of data like this .... 1,12,9 1,2,9 1,22,17,18 23,23,1,9 24,21,1 1,23,11 22,1 2,3 Is it possible to use function or command to split them into columns? 1 12 9 1 2 9 1 22 17 18 23 23 1 9 24 21 1 1 23 11 22 1 2 3 I don't want to use "TEXT TO COLUMN" as some of the addresses can be overwritten. Thanks so much, Regards, Elton Hi 'Elton Law' Try the below =TRIM(MID(SUBSTITUTE("," & $A$1& REPT(",",6),",", REPT(CHAR(32),255)),COLUMNS($B$1:...

How can I make columns that are separated by a line?
I need to make columns that are separated by a line. Is there a function on the toolbar that will do this? Insert a column between A and B Make it very narrow and draw a line from Drawing toolbar down the center of it. Or place a border down left side of cells of Column B Or post back with more detail. But no.............there is no toolbar function to separate adjacent columns. Gord Dibben MS Excel MVP On Fri, 29 May 2009 14:17:01 -0700, AdaptableAssistant <AdaptableAssistant@discussions.microsoft.com> wrote: >I need to make columns that are separated by a line. Is there ...

Passing info into a text field when doing the update query
Hello, I want a text boxt pop up in order to pass the information . The pop up will say : Notes taken by: I want it to do in one go This is my update query: INSERT INTO Catatan ( NO_URTANGT, TGLCATATAN, WKTCATAT, Subject, Notes ) SELECT a.NO_URTANGT, a.TGLCATATAN, a.WKTCATAT, a.Subject, a.Notes FROM [C:\Churchdata\Churchdataconso\BkEnd\Hahomion_be.mdb].Catatan AS a; Thanks for any help. -- Frank Situmorang Separate field for NotesBy: INSERT INTO Catatan ( NO_URTANGT, TGLCATATAN, WKTCATAT, Subject, Notes, NotesBy ) SELECT a.NO_URTANGT, a.TGLCATATAN, a.WKTCATAT, a.Sub...

Unlocking A Grid For Moving Text Around In A Chart: How, Please ?
Hello: Using MS Excel 97 SR-1 Guess this dates me pretty well,m but it does most everything I want. When making a graph, I sometimes put text notes in the graph. The text seems to be locking itself to a pre-ordained Grid. Is there any way to eliminate this grid locking, so I can move the text smoothly to where I want it ? Thanks, Bob Not sure if it is in Excel 97, but on the drawing toolbar in later versions there is an option 'snap to grid'. see if this helps -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hocke...

Column sorting incorrectly
I have a column containing numbers 1-40. I made sure the format of these cells is "Number" then I sorted my spreadsheet in ascending order. The strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear after 40. Why is this happening? Are you absolutely sure of the format? Sounds like the errant values are text. Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click sort it will select the current region which is determined by where xl finds blank rows and columns. To be sure, select the values and then sort. HTH -- Regards;...

Forumla rows and columns
Why in a column do I get a (0) result from a formula in one cell in the column and get a - for a result in the same column, different row with the same formula for each, the format for the entire column is accounting. I want all zero results in the column to be displayed as a dash (two results are (0) in the column and two other results to the same forumla show as the dash which is the correct format for accounting) -- Help !!!! The formula result is between 0.5 and -0.5. Formatting does not alter the real value. Wrap ROUND around your formula: =ROUND(your_formula_here,0) ...

Text width in custom MessageBox Class
Hey everybody, I�ve implemented my own MessageBox class in order to display custom icons. Everything works fine, the only problem I have is to determine how much space the text will need. I know about methods like DrawText(DT_CALCRECT) and so on but therefore I need a DC which I don�t have at that point, as I have to define the window rect before the window is actually created. I have also considered using the DC of the parent�s window but that seems a bit odd to me. What�s the regular way to handle this problem? Thanks for your help Sighn Try calling the GetDC() API to get a HDC...

Hidden Columns
Cannot open hidden columns by selecting the column adjacent to either side Is the worksheet protected (check Tools/Protection on the menu bar)? -- Rick (MVP - Excel) "FITZWALLY" <FITZWALLY@discussions.microsoft.com> wrote in message news:8BCE1A96-F336-4ACE-91E4-50BD04196383@microsoft.com... > Cannot open hidden columns by selecting the column adjacent to either side ...

Export from 2000 to 2008
I did an export of data from 2000 to 2008, all the tables and data copied over with the exception of the Identity attribute. (which is the auto number feature). This is not good, I tried to manually do it and it says that I need to drop and recreate table. This poses an issue given all data I have in the table and that needs to be imported over. What am I missing? MOCK (MOCK@discussions.microsoft.com) writes: > I did an export of data from 2000 to 2008, all the tables and data > copied over with the exception of the Identity attribute. (which is the > auto number fe...

How do I Chart a spreadsheet that has multiple columns of data
I have a spreadsheet that has multiple columns of data. What I have been asked to do is take the 6 categories and get the actual and standard data in a chart. Any help would be most apprciated. -- DMM Did you try selecting the data and running the chart wizard? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "soconfused" <soconfused@discussions.microsoft.com> wrote in message news:5BF238C3-6394-460D-928F-69CA1921BE9D@microsoft.com... >I have a spreadsheet that has multip...

What is controltip text? Looking for a field to store some text
I need a field in the properties to store a descriptive name of a textbox's contents. If a required field, which has "R" in the Tag field in the properties, has not been entered a message box appears: MsgBox "Please enter a value for " & ctl.Name Where ctl.Name is the name of the text box. l need to be able to say, for example, "Department Number" instead of "txtDeptno". I could put "Department Number" after the "R" in the tag and parse up tag but is there another field? How about ControlTip Text or Validation Text?...

column reference
I want to take the average of every other column in my data set and I will be adding weeks to the data as time moves forward. Is there a relative or absolute formula that does this? Thanks for any help. To average the odd columns: =AVERAGE(IF(MOD(COLUMN(3:3)*ISNUMBER(3:3),2),3:3)) even columns: =AVERAGE(IF((MOD(COLUMN(3:3),2)=0)*ISNUMBER(3:3),3:3)) Both of these are array formulas, and should be confirmed using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mike" wrote: > I ...

data in 1 column/cell to automatically repeat in another cell/col
i am adding clients names in colmn B and want that info to automatically repeat in column Q. Anyone know the formula in cell q2 =b2 copy down or a worksheet_change event right click sheet tab>view code>insert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 2 Then Exit Sub If Target.row < 2 Then Exit Sub Target.Offset(, 15) = Target End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Warthog" <Warthog@discussions.microsoft.com> wrote in message news:AF434D19-E550-48F9-81C2-E071CC33F594@microso...

Copy text and graphics boxes from Publisher to Word
I need to copy a sinigle column brochure from Publisher to Word. When I select all the boxes and copy, then open Word and paste, nothing comes over. When I try to save the file as a Word document (in Publisher), I get an error message telling me it can't be done because the document contains graphics. I can save the file as a pdf and then save the pdf to Word, but I don't get all of the graphics boxes. I need to send the file to someone and they don't have access to Publisher to edit it. Any help appreciated. Thanks. Copy and paste will work. -- Jo...

Changing color of text based on formula result
It' tough getting old. Can someone please refresh my memory as to how you change the color of a cell's contents based on the result of the formula? Thanks. Mark Mark, Conditional Formatting is what you're after. Take a look here for examples: http://www.contextures.com/xlCondFormat01.html John Mark wrote: > It' tough getting old. Can someone please refresh my > memory as to how you change the color of a cell's contents > based on the result of the formula? Thanks. > > Mark ...

editing text within a cell before moving to a new cell
Hi - Does anyone know how to move back and forth through text WITHIN a cell when first entering text in that cell? (If this is unclear, it's because I'm not sure quite the best way to explain it). Basically, imagine I type two words into a cell and realize I've mispelled the first one. If I hit the left arrow key (ostensibly to move back to the first word) excel will move me back an entire cell. I then have to (1) right-arrow over to the original cell, (2) hit F2, and (3) then scroll back to the misspelled word. Is there any key modifier I can use to skip directly to (3) without ...

line spacing w/in a text box
I'm having difficulty with line spacing within a textbox. I go to the Format menu and click line spacing and even if I adjust between lines and before & after paragraphs I still have way too much space between the lines. I'm designing a business card and am having to use a separate text box for each line on the card. I'd like to have just one text box for everytthing on the card. Can anybody help? THANKS! Barbara wrote: > I'm having difficulty with line spacing within a > textbox. I go to the Format menu and click line spacing > and even if I adjust...

Excel How do i make a range of cells proper text?
I have created an excell spreadsheet from Peachtree accounting. all the customer names are all caps. i want to select the whole worksheet and use a formula to make the whole sheet "proper" which will make the first letter of each work capitlaized and the rest of the work lower case. please be specific. i am not an excell expert , just a business owner ttrying to get by. Thanks, peter Hi, Peter. Try this: http://www.officearticles.com/excel/change_the_case_of_text_in_cells_in_microsoft_excel.htm There are several ways, and you can choose which one's best depending on how ...

Seeing Text File Names when Saving Excel Files
I am new to Excel 2007. I am saving a new Excel file and I want to give it a similar name to an existing text file so that I know by the filename that the two files are related to the same project. However, when I "Save As" the Excel file in the selected directory, the only other files I see in that directory are the other Excel files. I don't see any of the other files that I know are in there (word, powerpoint, pdf, etc.). In prior versions of Excel, I have been able to see all of the files in a destination directory....is that still possible with the 2007 version of ...

Can't get text string to recognize query result
Private Sub cmdConfirm_Click() Dim strMsg As String If Me.cboApproval = "Approved" Then strMsg = "Your meeting request has been approved." & (Chr(13)) strMsg = strMsg & "Meeting Date: " & Format([txtFTDate], "mm/dd/yyyy") & (Chr(13)) strMsg = strMsg & "Meeting Start: " & Format([txtFTStart], "h:mm AMPM") & (Chr(13)) strMsg = strMsg & "Meeting End: " & Format([txtFTEnd], "h:mm AMPM") & (Chr(13)) strMsg = strMsg & "Meeting Location: " & [txtFTBldg] &a...

Change Column Name
Can anybody advice me on how to go about change the column name in the worksheet. Instead of currently the column name is A, B, C I would like to change to for example A="Date" , B ="Name", and so forth to be shown on the worksheet. Thanks. LSB, It can't be done. You can change it to "1, 2, 3, etc." as opposed to "A,B,C, etc" by selecting R1C1 reference style. Suggestion......... Use column headers in the first row. If you want, you can get rid of the row and column headings altogether (meaning that your first row/header row will be all that...

Query: convert multiple logical fields into single 2 character text result
I've solved this particular puzzle, although I'd like to find a better solution than mine. So my emergency is resolved, nevertheless if you like puzzles, please read on... The school district wants a CSV file with fields in a specific order and with specific formatting. Here are the seven possible results for EthnicCd: 2 Char where ML = Multiple Races UN = Unknown or Declined to Report IN = American Indian or Alaskan Native AS = Asian or Pacific Islander BL = African American HI = Hispanic WH = White Our "People" database contains eight ethnicity Yes/No...

How do I eliminate automatic appearance of paragraph signs?
When I type in my text, automatically I get the paragraph sign appearing. At the same time, instead of a space between words, there is a dot that appears, not a period, but a dot, halfway up the line! How do I get back to standard typing? This is driving me crazy!!!! email domin@persona.ca Doug "In typing don't want dots in spaces!!!!" <In typing don't want dots in spaces!!!!@discussions.microsoft.com> wrote in message news:D65BB764-7767-4E48-94CF-6F8C546567F0@microsoft.com... > When I type in my text, automatically I get the paragraph sign appea...

Needed: Chart that combines clustered column and stacked column types
The chart is to show projected financials for a business. - Using Clustered Columns, I can show Revenue and Net Income. Not a problem. - Using Stacked Column, I can show the contribution to Revenue from each of the businesses two product lines. Not a problem What I would like to do is to combine these into a single chart: Show a Stacked Column with the Revenue contributions and next to it (Clustered Column) show Net Income. Is this possible? If so, can you please explain how to do this. Posting a simple example would be a bonus. Many thanks. gerry <at> pairofdocs <dot> net O...

Error number of columns does not match the header row! on Import
I am trying to import data from GoldMine into CRM and getting Error number of columns does not match the header row! can anybody help Many thanks in advance Hi Jim how are you importingthem are you using scribe, or a diffferent tool -- Alistair 2B.net http://www.crmdeployment.co.uk "Jimbo" wrote: > I am trying to import data from GoldMine into CRM and getting Error number > of columns does not match the header row! can anybody help > Many thanks in advance Check the thread of 20/3/2007 "Number of columns does not match the header row" I think it'l...