Concatenating text cells in a row (No. of cells not fixed)

  • Thread starter Thread starter ashish128
  • Start date Start date
A

ashish128

Hi,

I request you all to help me solve this problem.

I have an excel file with data in rows like following ;
A B
C D
1 This Is
Sample 2587
2 This Is
547
3 This 257
222
4 This Is
Sample text
This is sample
This is
This
This is sample text

Is there a way that I can concatenate only the text cells and ignore
the numeric ones (Per Row).

The number of cells containing text are variable (but they are in
starting only)

I would be grateful for your help.

Regards

Ashish Sharma
 
Sorry for that look of the message. Can anybody tell me what
precautions to take before posting so that my data comes in perfect
look.

Regards
 
Give this User Defined Function (UDF) a try...

Function ConcatenateTextOnly(R As Range) As String
Dim C As Range
For Each C In R
If Not IsNumeric(C.Value) Then
If Len(ConcatenateTextOnly) > 0 And Len(C.Value) > 0 Then
ConcatenateTextOnly = ConcatenateTextOnly & " "
End If
ConcatenateTextOnly = ConcatenateTextOnly & C.Value
End If
Next
End Function

If you are not familiar with UDFs, press Alt+F11 to go to the VB editor,
click Insert/Module from the VB editor's menu bar and copy paste the above
function into the code window that appeared. That's it. Now go back to your
worksheet and put this in E1..

=ConcatenateTextOnly(A1:D1)

and copy down. You can use any contiguous range you want and the function
will ignore empty cells and numbers, but it will concatenate everything
else.
 
Give this User Defined Function (UDF) a try...

Function ConcatenateTextOnly(R As Range) As String
  Dim C As Range
  For Each C In R
    If Not IsNumeric(C.Value) Then
      If Len(ConcatenateTextOnly) > 0 And Len(C.Value) > 0 Then
        ConcatenateTextOnly = ConcatenateTextOnly & " "
      End If
      ConcatenateTextOnly = ConcatenateTextOnly & C.Value
    End If
  Next
End Function

If you are not familiar with UDFs, press Alt+F11 to go to the VB editor,
click Insert/Module from the VB editor's menu bar and copy paste the above
function into the code window that appeared. That's it. Now go back to your
worksheet and put this in E1..

=ConcatenateTextOnly(A1:D1)

and copy down. You can use any contiguous range you want and the function
will ignore empty cells and numbers, but it will concatenate everything
else.

--
Rick (MVP - Excel)











- Show quoted text -
Click to expand...

Thank you so much Rick. It works great.

Thanks again

Regards
 
Back
Top