Prev: Excel 2007 Custom ShortCut Keys
Next: hisind rows with an if statement
From: Indynana on 4 Apr 2010 11:29
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: Gary Keramidas on 4 Apr 2010 11:35
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: tompl on 4 Apr 2010 11:41
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.
From: tompl on 4 Apr 2010 11:45
Sorry, that should have be Cell C2, not C3.

From: arjen van der wal on 4 Apr 2010 17:14

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.


 |  Next  |  Last
Pages: 1 2
Prev: Excel 2007 Custom ShortCut Keys
Next: hisind rows with an if statement