Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • DayName Date and Time
  • Pixel Excel Drawing Format Cells
  • Expenses calendar, FaCal Basic Math
  • WorksheetName and WorksheetsNo UDFs Lookup and References
  • Search across sheets in function ADDRESS
  • Edit directly in cell Excel User tips
  • Multiple-Validations (connected to each other) Data Validation
  • Limited Ceiling Basic Math

Fx to cut long column into two columns

Posted on March 19, 2012 By ANmar No Comments on Fx to cut long column into two columns

Convert one long column into two
This set of formulas will get the items after row 45 (for example) from column A into the column B starting from B1 to have two 45-rows next to each other instead of one 90-rows column

This was the answer for this question
Yahoo! Answers question http://answers.yahoo.com/question/index?qid=20081225182043AAc7ZEB

Show two columns in one page as four columns using functions only. This will help you to show the next x number of rows for a certain column to the next of the first one to reduce the number of pages to print.

Can work also to convert into 3 columns out of 1, etc

Use this formula to grap the first column
=OFFSET(OneCol!$B$1,((INT((ROW()-1)/$A$1)+1)-1)*$A$1+ROW()-1,0)
And use this one to grap the second column
=OFFSET(OneCol!$B$1,((INT((ROW()-1)/$A$1)+1))*$A$1+ROW()-1,0)
Again, all these formulas are linked to cell A1

 

Download spreadsheet
Excel Online version
Formulas - combined functions, INT, Lookup and References, OFFSET, ROW, Standard functions

Post navigation

Previous Post: iframe in Excel (XLiFrame)
Next Post: Week number to Sunday date – Weeknum reverse

Related Posts

  • IFS (and IFNA) to avoid nested IFs Formulas - combined functions
  • Sum of divide – Array formula Array formula
  • Calculate hours between two cells ABS
  • Get column name (columnname as A,B,C, etc) as input inside cell ADDRESS
  • Convert 2-column into wide (column-row) table Formulas - combined functions
  • CHOOSE – tree of decisions CHOOSE

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Blank (isempty) VS “” (null string) Excel User tips
  • Excel Dynamic Drop-Downs that grow with you COUNTA
  • Filter. Google Sheets function Google Sheets
  • CTRL+Y a mini macro Excel User tips
  • CountIf limitation COUNTIF
  • Sheet() and Sheets() Lookup and References
  • OFFSET+MATCH Formulas - combined functions
  • DayName Date and Time

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme