Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • MATCH fumction, just a simple search Lookup and References
  • Highlight dates dynamically AND
  • Hyperlink usage and HyperlinkOf UDF CELL
  • Excel keeps converting all cells to “Date” (or Time) Excel User tips
  • When Excel tells you to go get some life Excel User tips
  • Multiple-Validations (connected to each other) Data Validation
  • 6174 Kaprekar’s constant Basic Math
  • Shapes with dynamic output Excel User tips

MID + SEARCH to convert cell to rows

Posted on January 23, 2012 By ANmar No Comments on MID + SEARCH to convert cell to rows

I have been there and done that more than once before

This time I had a really large cell, more than 3k characters need to be converted into rows, and here is how you can do it.

This one uses MID with SEARCH to do that

Use one column to get the character number of the separator, I have it here as column C with formula:

=SEARCH("||",$D$2,C4)+2

And the other column to show the text between the new separator character location and the old one, I have it here as D with formula:

=MID($D$2,C5,C6-C5-2)

screenshots should say it all

MID, SEARCH, Standard functions, STRING, Texts and Strings

Post navigation

Previous Post: Hyperlink usage and HyperlinkOf UDF
Next Post: Standard math to calculate Aspect Ratio

Related Posts

  • Expenses calendar, FaCal Basic Math
  • Rotate table using 1 formula ADDRESS
  • State Abbr with Index+Match+Validation Data Validation
  • Week number to Sunday date – Weeknum reverse DATE
  • Extract Address into table Formulas - combined functions
  • 6174 Kaprekar’s constant Basic Math

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
  • Min Max vehicle Formulas - combined functions
  • Comments versus Notes Excel User tips
  • Hello world! Uncategorized
  • Looplist – Repeating months using formulas Basic Math
  • DayName Date and Time
  • Cell formats 0.0\% and 0.0%;(0.0%) Format Cells
  • Excel @ before function names in formulas Excel User tips
  • Walking Columns ActiveX controls

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme