Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Pixel Excel Drawing Format Cells
  • FORMULATEXT to show formulas FORMULATEXT
  • Progress bar in pure functions CHAR
  • When Excel functions are not enough Excel User tips
  • Looplist – Repeating months using formulas Basic Math
  • Return 12 values in one number AND
  • Workdays – Across months DATE
  • WorksheetName and WorksheetsNo UDFs Lookup and References

Extract Address into table

Posted on February 10, 2012 By ANmar No Comments on Extract Address into table

Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table

Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above

Which is usually what you got from any list of addresses online, and you want to convert it to table.

This is exactly what one of clients had and needed, so here is the file that does that.

Basically you need to have 5 columns with formula in column G as:

=OFFSET($B$1,(ROW()-5)*4+4,0)

to extract first line (Starting from cell B5

Then in column H as:

=OFFSET($B$1,(ROW()-5)*4+5,0)

Now, in column I you need to extract only city name, so you need this formula:

=LEFT(OFFSET($B$1,(ROW()-5)*4+6,0),SEARCH(",",OFFSET($B$1,(ROW()-5)*4+6,0))-1)

Then in J, use this formula:

=MID(OFFSET($B$1,(ROW()-5)*4+6,0),SEARCH(",",OFFSET($B$1,(ROW()-5)*4+6,0))+2,2)

To get state, then in K this:

=MID(OFFSET($B$1,(ROW()-5)*4+6,0),SEARCH(J5,OFFSET($B$1,(ROW()-5)*4+6,0))+3,5)

to get the Zipcode:

 

This way, you will convert all addresses into a table with address per row.

Formulas - combined functions, LEFT, Lookup and References, MID, OFFSET, ROW, SEARCH, Standard functions, Texts and Strings

Post navigation

Previous Post: Convert 2-column into wide (column-row) table
Next Post: Zipcode-State search

Related Posts

  • Validation based on another Validation COUNTA
  • Change Calculator – calculate change in multiple bills/coins Formulas - combined functions
  • Excel @ before function names in formulas Excel User tips
  • Sorting with functions only CHOOSE
  • CHOOSE – tree of decisions CHOOSE
  • Cuts string of list of items Formulas - combined functions

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
  • Custom fiscal year calendar (CFlex) Basic Math
  • iframe in Excel (XLiFrame) ADDRESS
  • Cell formats 0.0\% and 0.0%;(0.0%) Format Cells
  • Schedule column Basic Math
  • Get column name (columnname as A,B,C, etc) as input inside cell ADDRESS
  • GoLast to jump to last cell in a column CELL
  • Middle name + 1st name Basic Math
  • Convert 2-column into wide (column-row) table Formulas - combined functions

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme