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!

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" <(E-Mail Removed)> wrote in message
news:4308686B-4198-4428-A9C4-(E-Mail Removed)...
>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!
>
>

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.

Sorry, that should have be Cell C2, not C3.

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.

On Apr 4, 11:29*am, Indynana <Indyn...@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. *Theseblank
> 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)

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)
> .
>

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.
>

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" <(E-Mail Removed)> wrote in message
> news:4308686B-4198-4428-A9C4-(E-Mail Removed)...
> >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!
> >
> >
>
> .
>

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.
>
>