Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Blank (isempty) VS “” (null string) Excel User tips
  • Stale values (Scratched functions) Excel User tips
  • Get monthly total from table with dates DATE
  • When Excel functions are not enough Excel User tips
  • BMI Calculator Basic Math
  • Multiple-Validations (connected to each other) Data Validation
  • Multiple Dual-Validations COUNTA
  • Week number to Sunday date – Weeknum reverse DATE

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

  • SimpleANBox Basic Math
  • VALUE() of LEFT() Formulas - combined functions
  • Always get 1st name (or last) Formulas - combined functions
  • Middle name + 1st name Basic Math
  • CamelCase to Camel Case CHAR
  • New function IFERROR, finally IFERROR

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
  • Expenses calendar, FaCal Basic Math
  • Convert cell into textbox Excel User tips
  • iframe in Excel (XLiFrame) ADDRESS
  • Walking Columns ActiveX controls
  • Extract current (Active) workbook name CELL
  • SimpleANBox Basic Math
  • Multiple Dual-Validations COUNTA
  • Excel Dynamic Drop-Downs that grow with you COUNTA

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme