Multiple Line Texts to a single line??

Hi ! I have a multiple line texts I want to convert it in single line like for Example:

The following contents is to be converted in a single line

contents:

[aaaaaaaa] [bbbbbbb]......so on
[cccccccc] [ddddddd]......so on
[eeeeeeee] [fffffffffff]......so on

Result:

[aaaaaaaa] [bbbbbbb] [cccccccc] [ddddddd] [eeeeeeee] [fffffffffff]....so on


Thanks in advance

Hi mandukes,

If you are talking about the content of a single cell with multiple lines:

Function SingleLine(ByVal rng As Range)
    
    If rng.Cells.Count > 1 Then
        MsgBox "Only works with a single cell.", vbInformation
        Exit Function
    End If
    
    SingleLine = Replace(rng.Value, Chr(10), "")

End Function

Otherwise, if you want to concatenate a number of cells, let's say all the content of A1:B3, try this:

Function ConcatenateRange(ByVal rng As Range)

    Dim i As Long, str As String

    For i = 1 To UBound(rng.Value)
        str = str & Join(Application.Index(rng.Value, i), "")
    Next i

    ConcatenateRange = str
    'ConcatenateRange = replace(str,chr(10),"")

End Function

Cheers, berlan

[QUOTE=berlan;3643097]Hi mandukes,

If you are talking about the content of a single cell with multiple lines:

Function SingleLine(ByVal rng As Range)
    
    If rng.Cells.Count > 1 Then
        MsgBox "Only works with a single cell.", vbInformation
        Exit Function
    End If
    
    SingleLine = Replace(rng.Value, Chr(10), "")

End Function

Hi Berlan

"=SingleLine" function is not visible in Cell when I call it.

Using office 2013 x64

Regards


Edit : Working absolutely fine!! you are great

Thank you m8

Hi,

Assuming list is in A1:Bnn then in C1 copied across

Formula:

=OFFSET($A$1,(COLUMN()-1)/2-1,IF(MOD(COLUMN(),2)=1,0,1))

You can find your solution at the link below

http://www.excelforum.com/excel-gene...ml#post3635882