MARY KIRCHER RODDY
  • Home
  • Coaching and Research
  • Lectures
    • Upcoming and Past Presentations
  • Searching For Stories blog
  • Publications
  • Contact
  • Resources
  • Privacy Policy

Searching for Stories

Spreadsheet Magic - Text to Columns

5/24/2016

1 Comment

 
Have you experimented with importing data directly from Ancestry.com and FamilySearch.org as I explained in my blog posts,  “Spreadsheet Magic - Importing Data From Ancestry.com” and “More Spreadsheet Magic – The Importance of Exportance”?  Great tricks, huh? 

But there’s one thing I really don’t like about the imports and exports – the pieces of data, such as First-and-Last-Name or Date-Month-Year appear in one field.  That makes sorting the data difficult.  But there’s a trick for that.  Let me show you how.

Here are the first 20 or so results from my FamilySearch.org search for Roddys born in Ohio.  I’d like to be able to split the data from Column D into multiple columns for day, month and year.   (I’d also probably like to sort the names in columns B, I and J into separate columns, but I’ll show how to divide the dates as an example.)
  1. Determine how many columns you will need to insert to hold your “new” data.  In this example, it’s pretty easy.   I will want 3 columns, for day, month and year.  But if I were to sort on the father’s name column, the data might have multiple pieces, such as first name, middle name or initial, surname and perhaps a suffix like Jr.  Sometimes there might even be multiple middle names.  Examine your data carefully to make sure you insert enough columns.                                                                                         If you insert too few columns, you will overwrite date in the columns to the right.  It’s better to insert more columns than you need and delete the extra ones later than to insert too few columns!
  2. In my example I will highlight columns E, F and G to allow me to insert 3 columns.  On the Cells area of the Home ribbon tab, click on the triangle below the word “Insert” and click on “Insert Sheet Columns.”
Picture

​Now it looks like this:

Picture

Click on the Data tab at the top to expose the Data tools ribbon.  Highlight the column of text you want to split.  You can have as many rows as you want, but only one column.  Then click on the Text to Columns icon and it will bring up the first of 3 “Convert Text to Column Wizard” screens as shown below.
Picture

Excel is pretty good at figuring out what I want to do.  You can see 2 radio buttons – “Delimited” and “Fixed Width.”  Excel has looked at the data and seen that it is all divided into fixed widths – 2 units for the day, a space, 3 units for the month, a space and then 4 units for the year.  It is even all the way down.  (But if we wanted to split the Father’s Name column, we would likely want to use the delimited radio button because the words are of varying length but delimited by a space in between them.)  Excel even shows me a preview of what the data will look like.

Excel has guessed correctly what we want to do, so click “Next.”

This brings up Wizard box 2.  I could futz with this, but generally what Excel thinks I want to do is what I do want to do so I just click “Next.”  Now we are on the 3rd Wizard box.

​I like to keep my original column of data as it came out of the database, and then create new columns for the “divided” data.   In other words I will keep the column that has birthdate shown as 05 Oct 1875 in Column D, and then have 3 new columns for Day, Month and Year, showing “05” in column E, “Oct” in Column F and “1875” in column G.  This is my personal preference.  You might chose just to overwrite the original data.  In order to set up the new columns, I click on icon to the right of the destination box and then point to where I want the new data to begin, in this case $E$7 and then click Finish. 
Picture

​Here’s what my data looks like after the Text-to-Columns exercise is complete.  I can now sort the data by year, month and day to put these births in chronological order.

Picture

I hope these instructions help you to get more use out of your spreadsheets.  Text to Columns is a powerful tool, making your spreadsheets more usable with less typing.  Try it! 

​If you want to see more spreadsheet tools and tricks, you can check out my LegacyFamilyTree webinars at http://familytreewebinars.com/maryroddy

1 Comment
Diane Gould Hall link
5/24/2016 10:21:52 am

What an excellent post. Very helpful. Thank you so much. I had no idea Excel could do that. I have yet to import any information, but I know I will be soon.

Reply



Leave a Reply.

    Author

    Mary Kircher Roddy is a genealogist, writer and lecturer, always looking for the story.  Her blog is a combination of the stories she has found and the tools she used to find them.

    Read more of Mary's writings at "Adventures of A Broad Abroad" and at Letters from Limerick

    Archives

    April 2021
    January 2021
    November 2020
    August 2020
    July 2020
    February 2020
    January 2020
    November 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    October 2018
    September 2018
    August 2018
    July 2018
    February 2018
    January 2018
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    January 2016

    Categories

    All
    Achard
    Ahern
    Aldrich
    Amador County Genealogy
    Ancestry.com
    Archives
    Blair County
    Bradley Family
    Brannack
    Brannock
    Brown
    Brown Family
    California Genealogy
    Cemetery
    Census
    Citations
    City Directories
    Clark County
    Death Records
    DNA Strategies
    Education
    Enslaved People Research
    FamilySearch
    Family Stories
    Fields Family
    Freuhauf
    Genealogy Conferences
    Genealogy Education
    German Research
    Germany
    Graham Family
    Grandparents
    Hardy
    Hartmann
    Indexes
    Ireland
    Kircher
    Letters
    Lunenburg
    Map
    Mapping Tools
    Midwest Resources
    Midwives
    Military
    Newspaper
    Newspapers
    New York
    Ohio
    Pennsylvania
    ProGen
    Railroad
    Records
    Research Techniques
    San Francisco
    Sonoma County Genealogy
    Spreadsheets
    Springer
    Tiburon
    Timelines
    Virginia
    Virginia Genealogy
    Vital Records
    War
    Webster
    World War II Research
    Writing

     Subscribe in a reader

    Enter your email address:

    Delivered by FeedBurner

    Picture
Powered by Create your own unique website with customizable templates.
  • Home
  • Coaching and Research
  • Lectures
    • Upcoming and Past Presentations
  • Searching For Stories blog
  • Publications
  • Contact
  • Resources
  • Privacy Policy