Excel · MS Office · Scouting

Splitting Last name, First name from 1 cell to 2 in MS Excel

 

Here it is the end of August.  School has started for my kids and soon (next week) scouting will be in full swing.  With that comes the start of MANY fundraiser for the boy scouts.  As the Fundraiser Chair I have the privilege to over see all of the fundraisers that my sons troop does.  Luckily the troop has many parents who are willing to help run an event so I don’t have to coordinate all the fundraisers.

Today I am working on getting tickets ready to distribute to the boys for our annual Pancake Breakfast at the Lake Villa Civil War Days in September.  Since this is the first year I am in charge of printing the tickets and distributing them to the boys I’ve had some learning to do and some fixing to do.  It all started with the Excel file that contained the tickets to be printed. 

Problem 1: Some of the tickets weren’t getting updated when you made changes on the first ticket.

Solution: The problem tickets need their cells updated to reference the appropriate cell on the first ticket.

Problem 2: Getting a list of boys from TroopMaster by patrols that I can modify in MS Word

Solution: Save it as an .rtf (Rich Text Format)

Problem 3: Once I got the list of boys by patrols into Excel how do I split the LastName, FirstName from ONE cell to TWO cells.

Solutions:

Starting Cell contains *LastName, FirstNane
=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1)) FirstName
=SUBSTITUTE(LEFT(A1, SEARCH(",",A1,1)-1), "*", "") LastName (minus the comma and leading star in some cases)

Example:

image

image

In Excel it looks like this when you are done:

   image

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s