Good morning
I have been given access to a very large number of hand drawn pedigree charts compiled and drawn more than 30 years ago which have been uploaded to a website.
There is also, on the same website, a complete list of the Name and Surname of the content of these sheets. They are not in any particular order, just a continuous list of Surname and Christian name.
I have found that I can do a cut and paste of these details into an Excel spreadsheet but the data does not import into separate cells (separate Surname and Christian name), along with notes referring to the Pedigree Sheet where the Name/Surname appears.
I have gone through the cut and paste process in the Excel Spreadsheet and have used the find and replace function to delete all of the data but the Surname and Christian Name in each row.
Is there a function in Excel which will move the Surname and Christian Name in to separate cells.
If that is possible I am proposing to convert the resulting spreadsheet in to a Gedcom and upload it to Ancestry and create a Family tree to build on.
I have heard that āGarmpsā software can import a spreadsheet (CSV) and export it as a āGedcomā.
I am starting from scratch and would appreciate any thoughts/ideas/instructions on the process.
Thank you
Ron
On the assumption that there is a regular format to each row, then the answer is almost certainly yes. For example, if there is always a surname followed by one or more blanks followed by the Christian names, then there are functions to take all the characters before the first blank and put that in one cell and take everything after that into another cell, and then take everything after any blanks at the beginning of that second cell and put it into a third cell. (The second stage can be combined in a more complicated formula to eliminate the intermediate use of the second new cell, but the step by step approach makes it easier to get the formulas correct.)
If you can fully explain the format of your existing cell, perhaps someone more familiar with Excel functions can suggest the the formulas to use. I will see if I can refresh/remember how to do it.
Well, here is my amateur effort on the assumption that the first cell in each row contains a surname followed by ONE blank and then one or more first names.
Use the formula =LEFT(A1,FIND(ā ā,A1),-1) in cell B1 to select all the characters before the first blank
Use =RIGHT(A1,LEN(A1)-FIND(ā ā,A1)) in cell C1 to select all the characters after the FIRST blank.
Iām struggling at the moment to think how to handle more than one blank. IF that is a problem for you, hopefully someone will know the answer, as there are other String functions.
No guarantees with the above formulas. You may have to experiment a little with them, building them up as you discover what each element does and doesnāt do. Hope this helps.
I think the answer to that is to do a āReplaceā to change any double blanks to single blanks before applying your formula.
Iāve used the split() function in Google Sheets or Excel to break full names into their parts.
Both also can import text files and parse into cells on a user-defined separator, normally a comma or tab but could be a space.
Thereās no mention of RM in the body of your post. While it cannot import CSV via the UI, there are sqlite managers that can but you would need to know what you are doing.
There are several programs that can read spreadsheet data and convert that to GEDCOM, and Gramps is one of them. It was in fact this feature that made me convert from PAF to Gramps, which had the extra advantage of running in my own language, which is Dutch.
We have a manual for CSV import (and export) on our wiki at
and we also have a forum using the same software as this one at
I use RootsMagic to exchange data between Gramps and FamilySearch.
Excel has a Text-to-Columns menu option:
- Select the column containing the firstname lastname.
- Select the menu option Data / Text to Columns
- Select Delimited and click on Next
- Click in the Space box and click on Next
- Select General or Text (either works for text) and click on Finish
I knew that āwhere there is a will - there is a wayā. I am a very active User of Roots Magic.
I consider it my āgo-toā in genealogy.
In my opinion it is the āstandardā for my research and I use other programs to compliment what it currently cannot do for me.
It seems to be compatible with most other genealogy software.
I have the tools now I just have to learn to use them.
Thank you to all of you.
Cheers
On mac if you have a Numbers spreadsheet column (excel) with First and Last names you can paste that data into Textedit (wordpad). Then use Find/Replace to switch a space between the names for a comma. Now you have a CSV file. If desired you could copy the text, and in a spreadsheet with enough cells selected in 2 columns paste into the top cell.
This is also useful to clean up someone elseās badly formated email distribution list.
For Excel if you go to Data, Text to Column. You can easily move the second name from the first cell to the next column. You just tell it to split the cell on a space or if there are commas use that option you can see a preview of the results before clicking done. This will leave you with the actual data and not a formula that may not convert in Gedcom
Thank you for the information. It turned out to be a very simple operation to convert over 1500 Christian Names and Surnames from a listing of indigenous Australians in to my Ancestry.
I did a cut and paste of the complete list in to Excel and saved it in a āCSVā Excel File.
I then uploaded the file to an online genealogical site which converted it to a .gedcom file.
The whole process took about 30 seconds and I was able to download the converted data and upload it to Ancestry.
Within about 15-30 minutes Ancestry began to deliver āhintsā and this morning there were over 200 hints on the āAncestry Treeā for me to delve in to. A very simple process that anyone can use.
The conversion depended on entering the data in to a Template of ID, Surname, Given Name, Sex,Birth Date, Birth Place, Birth Note, Death Date, Death Place, Death Note and Other Notes
in that specific order.
Happy to share more information if anyone is interested. Thank you all for your input and interest.
Depending on the number of names you have it would be easier to have a header cell with āsurnameā and then tab to the cell on the right and label it āfirst nameā. Then input all the surnames (tab) first names under these columns until the end.
So there is no lineage at this stage but over 1500 unconnected, single-person ātreesā in your Ancestry Tree.
No Tomā¦but I wonāt bore you with the full details. I am sure you have much more important things to take care of. Just let it slide. 