Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
I have two columns of text that I want to combine into a third column. The
two columns of text may have blank cells mixed in with the text. These blank cells need to be ignored and not included in the combination. How do I combine the two columns into a single column? I have tried Range.Consolidate and Selection.Consolidate in VBA. In both cases, I can consolidate ranges of numbers, but not text. I've also tried the Consolidate command on the Data tab. I can't think of any formulas that will work. If you can think of a way to do this, PLEASE let me know. I will appreciate any help that you can provide. Thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
will a formula work? not sure i understand exactly what you want, try this:
=IF(OR(A1,B1=""),"",A1&B1) -- Gary Keramidas Excel 2003 "Indynana" wrote in message ... I have two columns of text that I want to combine into a third column. The two columns of text may have blank cells mixed in with the text. These blank cells need to be ignored and not included in the combination. How do I combine the two columns into a single column? I have tried Range.Consolidate and Selection.Consolidate in VBA. In both cases, I can consolidate ranges of numbers, but not text. I've also tried the Consolidate command on the Data tab. I can't think of any formulas that will work. If you can think of a way to do this, PLEASE let me know. I will appreciate any help that you can provide. Thank you! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
Assuming your data is in columns A and B and it starts in row 2. Put this
formula in Cell C3 then copy it down as far as needed. =A2&B2 You can then convert the formulas to text with paste special - values. Tom What do you mean easy, this is the most difficult question I have answered today. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
Sorry, that should have be Cell C2, not C3.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
Hi Indy, From your description I assume the text in the first two columns needs to remain intact and that the blanks only need to be removed from the concatenated text that will appear in the third column. The routine below works for me: Option Explicit Sub ConsolidateText() Dim rData As Range With Sheet1 Set rData = .Range(.Range("A1"), .Range("B1").End(xlDown)) End With Dim k As Long k = rData.Rows.Count Dim f As Long For f = 1 To k Dim sText1, sText2 As String sText1 = Sheet1.Cells(f, 1).Text sText2 = Sheet1.Cells(f, 2).Text Dim a As Integer a = Len(sText1) Dim b As Integer b = Len(sText2) Dim c As Integer For c = 1 To a If Mid(sText1, c, 1) = " " Then sText1 = Mid(sText1, 1, c - 1) & Mid(sText1, c + 1, a) c = c - 1 End If Next Dim d As Integer For d = 1 To b If Mid(sText2, d, 1) = " " Then sText2 = Mid(sText2, 1, d - 1) & Mid(sText2, d + 1, b) d = d - 1 End If Next Sheet1.Cells(f, 3).Value = sText1 & sText2 Next f End Sub Note that this routine assumes your data is in columns A and B as well as being on Sheet1, so you'll probably have to make some adjustments to the code. I hope this works for you. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
On Apr 4, 11:29*am, Indynana
wrote: I have two columns of text that I want to combine into a third column. *The two columns of text may have blank cells mixed in with the text. *These blank cells need to be ignored and not included in the combination. *How do I combine the two columns into a single column? *I have tried Range.Consolidate and Selection.Consolidate in VBA. *In both cases, I can consolidate ranges of numbers, but not text. *I've also tried the Consolidate command on the Data tab. *I can't think of any formulas that will work. * If you can think of a way to do this, PLEASE let me know. *I will appreciate any help that you can provide. * Thank you! You're over thinking it - simply trim each of the columns during the concatenation like so: =TRIM(B5)&TRIM(C5) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
The problem with using the TRIM function is that it only removes leading and
trailing blanks, other blanks mixed in the middle of the text will be left. I have two columns of text that I want to combine into a third column. The two columns of text may have blank cells mixed in with the text. These blank cells need to be ignored and not included in the combination. How do I combine the two columns into a single column? You're over thinking it - simply trim each of the columns during the concatenation like so: =TRIM(B5)&TRIM(C5) . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
Good Morning, Tompl,
First, thank you for trying to help me with this problem. Second, I aplogize for obviously not including an accurate description of what I am trying to do. I don't want to concatenate the cells. I want to combine the cells, so that all of the cells in Columns A and B are listed in Column C. Please see the example below. Col A Col B Col C 5 5 5 3 1 5 4 3 6 1 1 1 6 1 1 Again, I'm sorry that I didn't make this clear in my first post. Thank you! Indynana "tompl" wrote: Sorry, that should have be Cell C2, not C3. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
Good Morning, Gary,
First, thank you for trying to help me with this problem. Second, I aplogize for obviously not including an accurate description of what I am trying to do. I am using Excel 2007 with .xlsm file extension. I don't want to concatenate the cells. I want to combine the columns, so that all of the cells in Columns A and B are listed in Column C. Also, there should be no blank cells in Column C even though there may be some in Col A or B. Please see the example below. Col A Col B Col C 5 5 5 3 1 5 4 3 6 1 1 1 6 1 1 I have tried different variations of your formula, for example: =IF(A1<""),A1,IF(B1<"",B1,"") but the problem with this version of the the formula is that it will stop the first time it finds data in a cell. Again, I'm sorry that I didn't make this clear in my first post. Thank you! Indynana "Gary Keramidas" wrote: will a formula work? not sure i understand exactly what you want, try this: =IF(OR(A1,B1=""),"",A1&B1) -- Gary Keramidas Excel 2003 "Indynana" wrote in message ... I have two columns of text that I want to combine into a third column. The two columns of text may have blank cells mixed in with the text. These blank cells need to be ignored and not included in the combination. How do I combine the two columns into a single column? I have tried Range.Consolidate and Selection.Consolidate in VBA. In both cases, I can consolidate ranges of numbers, but not text. I've also tried the Consolidate command on the Data tab. I can't think of any formulas that will work. If you can think of a way to do this, PLEASE let me know. I will appreciate any help that you can provide. Thank you! . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Combine Columns of Text - sounds easy
Good Morning, Arjen,
First, thank you for trying to help me with this problem. Second, I aplogize for obviously not including an accurate description of what I am trying to do. I feel really bad because you went to a lot of trouble to write this code. I don't want to concatenate the data in the cells. I want to combine the columns, so that all of the cells in Columns A and B are listed in Column C, except for blank cells. Please see the example below. Col A Col B Col C 5 5 5 3 1 5 4 3 6 1 1 1 6 1 1 I am "re-doing" a spreadsheet that already contains a small amount of VBA code which I've been able to figure out until I got to this problem. The original spreadsheet was written in Excel 2002 with .xls extension. I am converting it to Excel 2007 with an .xlsm extension. The code from the original Excel 2002 spreadsheet will not work when it is copied into the Excel 2007 version. Below is the original code: Range("N6").Select Selection.Consolidate Sources:=Array( _ Range("BillableNumbers").Address(ReferenceStyle:=x 1R1C1, external:=True), _ Range("NonBillableNumbers").Address(ReferenceStyle :=x1R1C1, external:=True), _ Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False) When I run the code, the Excel 2007 debugger stops on the line that begins with "Function", specifically highlighting "=xlSum" and displays an error message, "Argument in ParamArray may not be named". If I relocate the "Function..." line of code into the first line, like this: Selection.Consolidate Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False Sources:=Array( _ the code appears to run with no errors, but it does not combine the two columns, either. I only know a little about VBA, and right now I'm in over my head, so if you can figure this out, I will be eternally grateful! Again, I'm sorry that I didn't include all of the information in my first post. Thank you! Indynana "arjen van der wal" wrote: Hi Indy, From your description I assume the text in the first two columns needs to remain intact and that the blanks only need to be removed from the concatenated text that will appear in the third column. The routine below works for me: Option Explicit Sub ConsolidateText() Dim rData As Range With Sheet1 Set rData = .Range(.Range("A1"), .Range("B1").End(xlDown)) End With Dim k As Long k = rData.Rows.Count Dim f As Long For f = 1 To k Dim sText1, sText2 As String sText1 = Sheet1.Cells(f, 1).Text sText2 = Sheet1.Cells(f, 2).Text Dim a As Integer a = Len(sText1) Dim b As Integer b = Len(sText2) Dim c As Integer For c = 1 To a If Mid(sText1, c, 1) = " " Then sText1 = Mid(sText1, 1, c - 1) & Mid(sText1, c + 1, a) c = c - 1 End If Next Dim d As Integer For d = 1 To b If Mid(sText2, d, 1) = " " Then sText2 = Mid(sText2, 1, d - 1) & Mid(sText2, d + 1, b) d = d - 1 End If Next Sheet1.Cells(f, 3).Value = sText1 & sText2 Next f End Sub Note that this routine assumes your data is in columns A and B as well as being on Sheet1, so you'll probably have to make some adjustments to the code. I hope this works for you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combine text from two columns to one | Excel Discussion (Misc queries) | |||
Change Format from Number to Percentage (sounds easy enough) | Excel Worksheet Functions | |||
sounds easy but I can't figure it out..... | Excel Programming | |||
combine two text columns | Excel Discussion (Misc queries) | |||
Help Please. How to save multiple html files (sounds easy but scenario causing confli | Excel Programming |