Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Cells with additional spaces AND
  • Excel Dynamic Drop-Downs that grow with you COUNTA
  • Sum of divide – Array formula Array formula
  • 0% interest promotion Basic Math
  • Skills Grid Conditional Formatting
  • Zipcode-State search Formulas - combined functions
  • Count Unique COUNTIF
  • Hello world! Uncategorized

Text-to-Columns, dynamically using formulas

Posted on April 3, 2018 By ANmar

I often use the technique of concatenating columns into 1 cell with separators.

Something like the CSV, 1 line that has all values for a single row (all columns for that row) into 1 text block.

And then, because of that, I need to extract that back, into table

I know there is “Text-to-Columns” in Excel, but I wanted that dynamically, so if source got changed, the columns output will be changed too.

So, here is the example, having three formulas in two columns.

Formula 1 in column E starting 11 down (Cell E10 just has the value of 1)

=IFERROR(SEARCH($D$8,$D$7,E10+1),"---")

Screenshot_2

 

Formula 2 in cell F11 only, to bring first item

=IFERROR(MID($D$7,E10,E11-1),"---")

Block2Rows6

 

Then, at last Formula 3, to get all additional columns from text block

=IFERROR(MID($D$7,E11+LEN($D$8),E12-E11-LEN($D$8)),"---")

Block2Rows5

 

I wanted to make it very clear and simple to show the case, data used in example is dummy data means nothing, just for sake of example.

 

Download spreadsheet
Excel Online version
IFERROR, Len, MID, SEARCH

Post navigation

Previous Post: Convert cell into textbox
Next Post: Worksheet name, dynamically using formula

Related Posts

  • Skills Grid Conditional Formatting
  • Get column name (columnname as A,B,C, etc) as input inside cell ADDRESS
  • Extract current (Active) workbook name CELL
  • Extract Address into table Formulas - combined functions
  • Worksheet name, dynamically using formula CELL
  • Hyperlink usage and HyperlinkOf UDF CELL

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
  • Jan8 date format AND
  • Recover the unrecoverable Excel User tips
  • Filter. Google Sheets function Google Sheets
  • Workdays – Across months DATE
  • Calculate hours between two cells ABS
  • Excel Dynamic Drop-Downs that grow with you COUNTA
  • Extract Address into table Formulas - combined functions
  • Min Max vehicle Formulas - combined functions

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme