+ Reply to Thread
Results 1 to 4 of 4

transpose column and insert text in between each cell value

Hybrid View

  1. 06-14-2016, 01:55 PM #1
    cellmatrix
    cellmatrix is offline
    Registered User
    Join Date
    10-29-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    2

    Post transpose column and insert text in between each cell value

    Hi everyone!

    Col1 Col2 Col3
    2210 1240 7776
    3320 2255 6654
    4430 3465 5543
    5540 5670 4433
    6650 6780 3322
    7780 8760 2230
    9940 7845 1120

    How can I turn the above column of values into a combined single cell with the word "OR" in between so that I can copy and paste into Windows Explorer search box? Once Windows search is complete, I click on the matched filename and sent to printer.

    Example of desired result for 1st column: 2210 OR 3320 OR 4430 OR 5540 OR 6650 OR 7780 OR 9940
    Example of desired result for 2nd column: 1240 OR 2255 OR 3465 OR 5670 OR 6780 OR 8760 OR 7845

    Please let me know if it's not clear. Thanks for your help.
    Reply With QuoteRegister To Reply
  2. 06-14-2016, 02:11 PM #2
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: transpose column and insert text in between each cell value

    Hi
    Considere your data in A2:C8, and D1:F1 a empty cells
    Use in D2 and copy down and right (D2:F8)
    Formula: copy to clipboard 
    =D1 & A2 & IF(A3<>""," OR ","")

    Cells D8, E8, F8 has the desired results.
    Reply With QuoteRegister To Reply
  3. 06-14-2016, 02:31 PM #3
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: transpose column and insert text in between each cell value

    Something like this would require VBA unless you have Excel 2016 (with Office 365 subscription) where you can take advantage of the new functions such as TEXTJOIN or CONCAT
    So, here is workaround with additional columns
    Assuming Data is A2:C8
    Enter formula in D2 and pull it across to cell F2 and then down
    Formula: copy to clipboard 
    =A$2:A8&IF(A3="",""," OR ")


    then formula in G2
    Formula: copy to clipboard 
    =CONCATENATE(D2,D3,D4,D5,D6,D7,D8)

    then in G3
    Formula: copy to clipboard 
    =CONCATENATE(E2,E3,E4,E5,E6,E7,E8)

    and in G4
    Formula: copy to clipboard 
    =CONCATENATE(F2,F3,F4,F5,F6,F7,F8)

    v A B C D E F G
    1
    2 2210 1240 7776 2210 OR 1240 OR 7776 OR 2210 OR 3320 OR 4430 OR 5540 OR 6650 OR 7780 OR 9940
    3 3320 2255 6654 3320 OR 2255 OR 6654 OR 1240 OR 2255 OR 3465 OR 5670 OR 6780 OR 8760 OR 7845
    4 4430 3465 5543 4430 OR 3465 OR 5543 OR 7776 OR 6654 OR 5543 OR 4433 OR 3322 OR 2230 OR 1120
    5 5540 5670 4433 5540 OR 5670 OR 4433 OR
    6 6650 6780 3322 6650 OR 6780 OR 3322 OR
    7 7780 8760 2230 7780 OR 8760 OR 2230 OR
    8 9940 7845 1120 9940 7845 1120
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  4. 06-14-2016, 04:36 PM #4
    cellmatrix
    cellmatrix is offline
    Registered User
    Join Date
    10-29-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    2

    Re: transpose column and insert text in between each cell value

    @José Augusto: Your method works!

    @AlKey: Great step-by-step tutorial with visual aid. I wish I could code VBA. Need to enroll in night class at my local community college for some VB lessons.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro with text to columns, insert rows, and paste special>transpose
    By Schern in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-21-2016, 11:43 AM
  2. [SOLVED] VBA Insert multiply rows and Transpose from Row to Column
    By TCookie in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-14-2015, 02:44 PM
  3. Insert rows and then transpose data from a horizontal row into a column
    By Winescape in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-13-2014, 11:45 PM
  4. [SOLVED] Macro with text to columns, insert rows, and paste special>transpose
    By diaphanous1138 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-22-2013, 02:49 PM
  5. How to insert text under the last used cell of a column using vba
    By skavety in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 03-10-2013, 09:58 PM
  6. [SOLVED] Need Help w/Macro that Insert Rows, Copy Row, and Transpose to Column
    By hiroki3296 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-01-2012, 10:27 AM
  7. [SOLVED] Insert cell text every 5 rows in a column
    By Mike Mike in forum Excel General
    Replies: 2
    Last Post: 08-04-2006, 03:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1