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

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

Required Result
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

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

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

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

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)


"ashish128" wrote in message
...
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

Required Result
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

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

On Sep 4, 2:41*pm, "Rick Rothstein"
wrote:
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)

"ashish128" wrote in message

...



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

Required Result
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- Hide quoted text -

- Show quoted text -

Thank you so much Rick. It works great.

Thanks again

Regards