Prev: Excel 2007 Custom ShortCut Keys
Next: hisind rows with an if statement
From: Jef Gorbach on 4 Apr 2010 18:26
On Apr 4, 11:29 am, Indynana <Indyn...(a)discussions.microsoft.com>
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)
From: arjen van der wal on 4 Apr 2010 19:15
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)
> .
>
From: Indynana on 5 Apr 2010 08:45
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.
>
From: Indynana on 5 Apr 2010 08:58
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" <Indynana(a)discussions.microsoft.com> wrote in message
> news:4308686B-4198-4428-A9C4-65BB19D02DD9(a)microsoft.com...
> >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!
> >
> >
>
> .
>
From: Indynana on 5 Apr 2010 09:56
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:=x1R1C1,
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.
>
>
First  |  Prev  | 
Pages: 1 2
Prev: Excel 2007 Custom ShortCut Keys
Next: hisind rows with an if statement