Reverse text to columns

Is there any way to combine text from multiple columns into one column? Sort
of like the reverse of or undoing the "text to columns" feature.

I know I cn use the "&" symbol in a formula  e.g.  =A1&B1&C1   but this
leaves a formula in the cell  so if I delete the original cells it no longer
works.

I'd like to simply merge columns that split text into multiple columns back
into one column.

Thanks!!



0
10/7/2003 4:23:19 AM
excel 39879 articles. 2 followers. Follow

2 Replies
1969 Views

Similar Articles

[PageSpeed] 34

But you can simply copy>paste special>values in place
to overwrite the "&" formulas to convert to values?

Rick Bedard <rickbedard@mindspring.com> wrote in message
news:erXx9qIjDHA.2416@TK2MSFTNGP10.phx.gbl...
> Is there any way to combine text from multiple columns into one column?
Sort
> of like the reverse of or undoing the "text to columns" feature.
>
> I know I cn use the "&" symbol in a formula  e.g.  =A1&B1&C1   but this
> leaves a formula in the cell  so if I delete the original cells it no
longer
> works.
>
> I'd like to simply merge columns that split text into multiple columns
back
> into one column.
>
> Thanks!!
>
>
>


0
demechanik (4694)
10/7/2003 4:33:58 AM
One way, using a macro:

    Public Sub ColumnsToText(Optional rRng As Range, _
                Optional sDelim As String = "")
        Dim vTxtArr As Variant
        Dim nTop As Long
        Dim i As Long
        Dim j As Integer
        If rRng Is Nothing Then Set rRng = Selection
        Set rRng = Intersect(rRng, rRng.Parent.UsedRange)
        vTxtArr = rRng.Value
        nTop = UBound(vTxtArr, 1)
        For i = 1 To nTop
            For j = 2 To UBound(vTxtArr, 2)
               vTxtArr(i, 1) = vTxtArr(i, 1) & sDelim & vTxtArr(i, j)
            Next j
        Next i
        ReDim Preserve vTxtArr(1 To nTop, 1 To 1)
        rRng.Resize(, 1).Value = vTxtArr
    End Sub

In article <erXx9qIjDHA.2416@TK2MSFTNGP10.phx.gbl>,
 "Rick Bedard" <rickbedard@mindspring.com> wrote:

> Is there any way to combine text from multiple columns into one column? Sort
> of like the reverse of or undoing the "text to columns" feature.
> 
> I know I cn use the "&" symbol in a formula  e.g.  =A1&B1&C1   but this
> leaves a formula in the cell  so if I delete the original cells it no longer
> works.
> 
> I'd like to simply merge columns that split text into multiple columns back
> into one column.
> 
> Thanks!!
> 
> 
>
0
jemcgimpsey (6723)
10/7/2003 4:54:10 AM
Reply:

Similar Artilces:

Dual boot in reverse
Hello, I have dual boot system: driveC --> WinXP driveD --->Vista Now, I do not need WinXP anymore. I want to have only Vista and combine C & D into one, single partition. How to do that without reinstalling Vista? Your thoughts appreciated, Claire On Sun, 17 Jan 2010 15:00:12 -0500, "Claire" <replyto@fra> wrote: >Hello, > I have dual boot system: >driveC --> WinXP >driveD --->Vista >Now, I do not need WinXP anymore. >I want to have only Vista and combine C & D into one, single partition. &...

Finding Duplicated Text within Columns
I'd be grateful if anyone could help me with the following: I have 2 columns, both contain a number of items, some the same, some not; eg: Column A: Column B: Row 1: apple pear orange apple orange Row 2: orange banana banana orange Row 3: peach banana melon grape apple orange I need to compare the columns and find which rows are NOT exactly the same and, preferably, to identify the differences. For instance, in the above example, I would ignore Row 2 as the cells contain the same items (albeit in a different order), but I w...

Is it possible to assign rows for exclusion when data is sorted by column?
I have an inventory of books, listed under cells which bear heading like "history", "science", etc. [image: http://www.parpacy.com/excel001.jpg] Is it possible to assign the rows which contain headings some kind o exclusion status so they stay fixed while the data shifts when it i sorted +------------------------------------------------------------------- |Filename: excel001.jpg |Download: http://www.excelforum.com/attachment.php?postid=3962 +------------------------------------------------------------------- -- Push ...

vertical text not drawn at correct location
The code below illustrates the problem I am having. A rectangle is drawn in the center of the display. Text is suppose to be drawn at a 270 degree angle and centered in the middle of the rectangle. Instead, the text is always drawn to the far to the left of the rectangle. If I change text escapement to 0 degrees then the text is correctly centered. Can someone suggest what mistake I am making? Thanks in advance. Ian CMyView::OnDraw( CDC *pDC ) { // create font CFont oFont; LOGFONT oLF; memset( &oLF, 0, sizeof(oLF) ); oLF.lfHeight = 25; oLF.lfOrientation = ...

Formating Text in a List Box
How do I increase or format the text in a list box? Thanks! In the Properties Box you can set the Font, Style and Size. PC "PSTEWART" <PSTEWART@discussions.microsoft.com> wrote in message news:C26CF8F6-8C68-4809-9F49-54041CCCE9B1@microsoft.com... > How do I increase or format the text in a list box? Thanks! Thanks for your post. When I create a "list box" from the Forms toolbar, the properties box does not have any options to change the font size. I am assuming that the text size is set to the default text (font and size) of the workkbook.. "PC&...

Is there a quick way to delete all duplicate rows in a column?
Column A consists of names; Column B consists of dates. If a name is registered on more than one occasion, there will be two entries with same name, different dates. Is there a quick way to delete these duplicates other than sorting Col. A alphabetically and deleting them manually? In C1 add =IF(COUNTIF($A$1:A1,A1)>1,"Dup","") and copy down then filter on column C, and select Dup, and delete visible rows -- HTH RP (remove nothere from the email address if mailing direct) "Jellaby" <Jellaby@discussions.microsoft.com> wrote in message news:...

Converting Numerical Values Into Text #2
Genius! I've just tried it, and it worked (I had to copy and past special into a text formatted column before it would import into custom list, but it worked). Thank you so much! Cle -- cleadavi ----------------------------------------------------------------------- cleadavis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1658 View this thread: http://www.excelforum.com/showthread.php?threadid=31446 ...

REVERSING TEXT
HOW CAN I REVERSE TEXT AND PICTURES ON PUBLISHER TO PRINT ONTO T.SHIRTS spike <spike@discussions.microsoft.com> was very recently heard to utter: > HOW CAN I REVERSE TEXT AND PICTURES ON PUBLISHER TO PRINT ONTO > T.SHIRTS YES. PLEASE READ REPLY TO YOUR FIRST POST. Please do not talk in all caps. It is considered shouting and makes your post difficult to read and is rude. Your first message ("how do i reversie pictures and text for printing to t.shirts") is directly below this one and has been answered by yours truly. -- Ed Bennett - MVP Microsoft Publisher ...

How do I match words in Column A and C in Excel 2003
I want to do a comparison on my excel sheet. I have 4 columns and I need to compare the single words in column A to match the single words in column C and the single word in Column D on the respective row. Is there a formula to do this? eg it starts like 1 Dog Tail Bird Tweetypie 2 Cat Tail Dog Rover 3 Bird Feather Cat Tom but ends up looking like 1 Dog Tail Dog Rover 2 Cat Tail Cat Tom 3 Bird Feather Bird Tweetypie I have looked at vlookups to be honest dont fully understand wh...

Text Formatting when using CONCATENATE
I have several columns of data and I'm using the CONCATENATE function to combine the data into one column on the right, which is simple enough. But I need to extend on this by keeping the formatting of the text as it appears in the original columns. For example, if Column A is Bold (*ONE*), but Column B is italic (-TWO-), how can I have the text in the result column keep the original formatting (*ONE* -TWO-). This is a problem which has had me lost for ages, so any help or suggestions would be greatly appreciated. Steve --- Message posted from http://www.ExcelForum.com/ Steve, You ...

Importing text file corrupts my database
I'm running Entourage in Office for Mac 2008 on a MacBook under Snow Leopard 10.6.2. First I cleared out a lot of old emails and folders but there were three emails I could not delete. This then linked to the database being or becoming corrupted and rebuilding it failed every time very near the end. I eventually resolved that by manually dragging every folder to make mbox files and constructing a new identity - never done that before. The new database is fine and uncorrupted, BUT, every time I import my list of contacts from a delimited text file the database corrupts again ...

CTreeCtrl text backwards
Hi, I am using a CTreeCtrl, and setting the text for a tree item as follows: SetItemText(hTreeItem, "AAA BBB !!!"); Unfortunately, when the tree item is displayed, the exclamation marks appear at the beginning of the string, instead of at the end of the string ("!!! AAA BBB"). What's going on? Thanks. Don Works fine here.. show us the surrounding code. Rail -- Recording Engineer/Software Developer Rail Jon Rogut Software http://www.railjonrogut.com mailto:rail@railjonrogut.com "Don Gurr" <__dgurr@veriteq.com__>...

Formulas reversed
Using Office 2007 and windows XP. The back slash has become "multiply" and the star has become "divide". In other words, these two symbols are reversed. Have no idea how this happened. How can it be reversed back to normal? thanks. Please give an example of formula, values of input cells and result of formula -- Kind regards, Niek Otten Microsoft MVP - Excel "RENEE" <RENEE@discussions.microsoft.com> wrote in message news:C56D666E-70CD-4E8F-97F9-1FAD9CE92024@microsoft.com... > Using Office 2007 and windows XP. The back slas...

How to display a text field of numbers as text?
I have a table in which data are entered and categorized according to a cost code, e.g., 14.2, 21, 14.2.6.4, etc. I created a combo box (I also tried a text box) so the user can only enter in the correct cost codes. The data type of the cost code is text because a number with multiple decimal points will not work The problem is that when I run a query to gather records, the resulting table does not conatin the text of the cost code but rather a number of which I know not where it comes from. I tried format properties indicating that a character is not needed but that did not work. ...

INSERTING ROWS OF TEXT
HOW DO YOU INSERT ROWS OF TEXT BETWEEN ROWS WHEN YOU NEED T O ADD LINES FOR EXAMPLE IF I NEEDED TO 2 MORE ROWS BETWEEN 6 AND 7 Joe, Please don't post in all caps -- it is considered rude and is hard to read. You can add add two rows between rows 6 and 7 by selecting selecting rows 7 and 8 and choosing Row from the Insert menu. -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com chip@cpearson.com "JOE" <PLAN@SBSGLOBAL.NET> wrote in message news:03a201c38e8d$dff368b0$a101280a@phx.gbl... > HOW DO YOU INSERT ROWS OF TEXT BETWEEN ROWS WHEN YOU NEED &g...

Column to Array
I have spent HOURS on this... Very simple, all I need to do is convert a column of about 1600 items to an array 12 items wide. Help??? doneagle@gmail.com wrote... .... >Very simple, all I need to do is convert a column of about 1600 items >to an array 12 items wide. 1600 isn't divisible by 12. I'll assume there are 1608 items. I'll assume your column of data is named D. For what purpose? If you just want to fill another range that spans 12 columns by 134 rows (I'll use G1:R134). If you want to go across then down, use G1: =INDEX(D,1) H1: =INDEX(D,COUNTA($G1:G1,1)) ...

Count Text Data
Using 2007 on Vista If I've got text data which in some columns either has data or there is a blank, what formula do I use to count how many cells have text in them per column? Many thanks =COUNTA(A2:A200) will count everything except blanks post back if you have numbers as well that should not be counted -- Regards, Peo Sjoblom <weewillie@anon.com> wrote in message news:lfm764d39m4prld1iiqllpjuen7a1eptoq@4ax.com... > Using 2007 on Vista > > If I've got text data which in some columns either has data or there > is a blank, what formula do I use to count...

LOOKUP a column from another worksheet
Help please i want to show a column from a worksheet based on a TRUE/FALSE Condition . That is if the value in row2 is TRUE show the column ( im wanting to show this data on a different worksheet within the same workbook) does this make sense ?? =IF(Sheet2!B1=TRUE,Sheet2!A1,"") Put this say in sheet1 A1 It reads in cell A1 from sheet2 if the statement "TRUE" is in cell B1 of sheet2 If B1 reads "FALSE" it will stay blank ("") "UTPC01" wrote: > Help please i want to show a column from a worksheet based on a TRUE/FALS...

Possible to reverse a row of entries?
Is there a command or format command that will let me take a row of entries and reverse them? In other words if I have rows #1 through #10 top to bottom, I now want them to go #10 to #1 top to bottom. thanks Rick Hi in B1 enter =OFFET($A$10,-(ROW()-1),0) and copy down for ten rows -- Regards Frank Kabel Frankfurt, Germany rs wrote: > Is there a command or format command that will let me take a row of > entries and reverse them? In other words if I have rows #1 through > #10 top to bottom, I now want them to go #10 to #1 top to bottom. > > thanks > > Rick Ric...

Reverse arrow for hyperlinks
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I need to find a 'reversing arrow' that I can add to the toolbar to click on after using a hyperlink. <br> You can do this in Windows but I cannot find a similar facility when using my macbook. <br> I use longish word docs that often have links to other parts of the document. How do I get back to the point at which I activated the hyperlink, apart from scrolling back laboriously? <br> Thanks for any solutions. Shift + F5, provided you have not closed the document. Shift + F5 tak...

Add row after column of duplicate info
I want to know if there is a way to have Excel automatically insert a blank row after a column if the number in the column changes. Example: Column B 110011 110011 110011 110345 110347 110347 Would like for it to automatically insert as below: 110011 110011 110011 Insert blank row here 110345 Insert blank row here 110347 110347 Insert blank row here Can anyone help with this? It would be greatly appreciated :) Debe Data|subtotals can add a line with subtotals between cells with common values. You can use the outline symbols to show just the subtotal line, select those visible rows ...

Reverse engineering
I have to document an Access 97 that has grown like wild blackberries. The database's creator has left us no documentation at all and I would like Visio to extract as much information as possible. So far I have extracted a good UML of the tables. Currently I am doing screen dumps of the forms to show what the screens look like - then pasting them into Visio. What else can I do? Can I extract the queries, forms, reports, macros and modules also - and save and print these out from Visio? Please advise. As I remember there was a "database documenter" with Access. I don...

Can I reverse the order of data in a set of cells?
I have a column of cells filed with data. I know I can transpose the data 90 degrees, but is there a way to cut or copy the data in the cells and paste it so the values are in the opposite order, i.e. a 180 degree difference? Thanks to anyone who can help. I am using Excel 2003. One way, insert a new column or use an existing, number it from 1 - n (where n is the last index value you want to reverse the order of, so if your column is 100 rows number the help column 1, 2, 3 - 100) select both columns and sort descending on the help column -- Regards, Peo Sjoblom "salwitt"...

how to set columns that will stay on screen?
I notice a few Excel files created by others have this feature that the first 2 columns at the left will not move when scroll it horizontally. The scrolling only starts column C. It's great for column or row with labels. How is it set? How to disable it? Thanks, On Apr 8, 5:04=A0pm, liu <spamfree...@yahoo.com> wrote: > I notice a few Excel files created by others have this feature that > the first 2 columns at the left will not move when scroll it > horizontally. The scrolling only starts column C. It's great for > column or row with labels. How is it set? How to di...

Text import Wizard
Hi Guys Can someone help? I have an excel spreadsheet that when originally opened automatically activated the Convert Text to Columns Wizard. But now when opened it doesn't activate the Text Wizard even though the text is still in one column. Is it because Excel senses that the spreadsheet has already been through the conversion? Is there a way I can get Excel to automatically start this wizard when I open my spreadsheet. I look forward to hearing from you. Thank U 1. To activate the Convert Text-to-Col wizard manually: a. select the column b. click Data > Text to Columns ...