Alternative to using the & sign

[NewTeacher]

Can anyone advise on an alternative method to use to show all the letters as one sentence (as shown) instead of using the really long formula that has been provided.
=A1&B1&C1&D1 etc

Many Thanks
Anu

 

[oldchippy]

Hi NewTeacher,

Welcome to the forum, please take time out to read the rules of the forum if you haven't already, here is the link

http://chandoo.org/forum/forums/new-users-please-start-here.14/

With regard to your question, try this

Select cell G1 and type
=TRANSPOSE(A1:D7) in the formula bar
Press F9 and you will see all the letters in the formula bar
Delete the curly brackets at the beginning and at the end
Type after the = sign CONCATENATE and insert an opening bracket and also a closing bracket at the end of the formula bar
Press Enter

 

[NewTeacher]

Hi there

I tried that one already but that provides the decrypted answer ie (computing is good for you) what i want to see is the encrypted code (which is read along the rows rather than down the columns) so it looks like
CNO*OGOYM*DOPI*UUSF*T*O*IGR*

thanks

 

[oldchippy]

OK in that case, do this first with your data

Select the data, copy, select cell A12, paste special, transpose. Now carry out my first answer on that data. BTW I forgot to mention to replace the ; with just a , but I assume you worked that one out for yourself.

 

[SirJB7]

Hi, NewTeacher!

If you're willing to use an UDF place the following VBA code in any standard module:

Option Explicit

Public Function sNewPupil(prRange As Range, psWise As String) As String
    ' constants
    Const ksRowWise = "R"
    Const ksColWise = "C"
    ' declarations
    Dim I As Integer, J As Integer, I1 As Integer, J1 As Integer, A As String, B As String
    ' start
    A = ""
    ' process
    With prRange
        Select Case psWise
            Case ksRowWise
                I1 = .Rows.Count
                J1 = .Columns.Count
            Case ksColWise
                I1 = .Columns.Count
                J1 = .Rows.Count
        End Select
        For I = 1 To I1
            For J = 1 To J1
                Select Case psWise
                    Case ksRowWise
                        B = prRange.Cells(I, J).Value
                    Case ksColWise
                        B = prRange.Cells(J, I).Value
                End Select
                Debug.Print B;
                A = A & B
            Next J
        Next I
        Debug.Print
    End With
    ' end
    sNewPupil = A
End Function

Usage:
For B9: =sNewPupil(A1:D7,"C") ... i.e., column wise
For B10: =sNewPupil(A1:D7,"R") ... i.e., row wise

Just advise if any issue.

Regards!

PS: BTW, playing a bit with transposition and substitution? If so and you actually want to defeat frequency analysis attacks you should:
a) Switch from monosyllabic to bi, tri or variable syllable per character
b) Mask the result with any bijective shifting method, i.e., reversible and univocal