[techtalk] EXCEL/G-Sheets – Split Cell Contents

Wed Apr 12 09:42:01 PDT 2023

Welcome to the latest issue of Tech-Talk!
Reminder: When accessing resources at the Tech-Talk website<https://www.tech-talk.com/login/oregon>, use ORLIBTECH
for both the username and password when prompted.

Tech-Talk is a paid subscription service for staff of Oregon libraries and is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon.
How to quickly separate content in one cell

[Tech-Talk 27 years]

[video]<https://r20.rs6.net/tn.jsp?f=001FWiLaWtlajNk-GsfvFfNmHkusdoIX71fQA8KO93YD4NILTyNsi7KdG4JtX4BAOs6zSRrQGO5AialBUFQR_NGoBXMhDWDR2WuJt1z7aM4WoMD-m5LnANUl8LbnFkP4G4LTpRoYOoxV0L09OdYyW9LFsxVr6IGUmswJlhBHSYzKsbuD646T-VzDTJ6G69S3juP&c=b4Xw9YcWDajXh3hCiacR5vPj1rk-XytrOnpTWPDgZ5Jb8daXjoKLlg==&ch=MhsID6eUGt3_Wp5LTPQvmJ-BLLJczUvhhQsikPZ87k5rRo94j1lyow==>

Photo by Shannon Potter<https://r20.rs6.net/tn.jsp?f=001FWiLaWtlajNk-GsfvFfNmHkusdoIX71fQA8KO93YD4NILTyNsi7KdCk60mJsj3TKVimkKEzmc7kOLxfX3A250M4ROuqT74IsIWrVmMgNPUR0eXkLgGbqPDmr6U3vvEcEuXCoVWzZVW8p2_W2CcepEinqz84urhGHBUivb9ml74S5Yk95d5zzWX3SU3VbnjnwPMizdX0vDNMpMpklfueKZHei8_UwPabOPYUuJiqJKDiwAUtqrrAytJXoTUdk4DZ1mYwyhRRimkY=&c=b4Xw9YcWDajXh3hCiacR5vPj1rk-XytrOnpTWPDgZ5Jb8daXjoKLlg==&ch=MhsID6eUGt3_Wp5LTPQvmJ-BLLJczUvhhQsikPZ87k5rRo94j1lyow==> on Unsplash<https://r20.rs6.net/tn.jsp?f=001FWiLaWtlajNk-GsfvFfNmHkusdoIX71fQA8KO93YD4NILTyNsi7KdCk60mJsj3TKuX7z05Wo7vQaz54BVgxIUKySeQdyODIwDTOEn3MTWw01ngE2lP-nOAhiNUN-X9LqLyA_lUNcLpVn2rIIAj3AsY6cGd18MP63QGyhnqfiS46Y_ZY-TZrTV5Ed4NlWikjovIUvkv0CvtIX1lKAfAJUPCb6kH1oK2JA9TIb4aCf-KdNWnv8SpiTQAfz7hVwFWfdbUwdm-Oxhg4zhcxeWqRQEw==&c=b4Xw9YcWDajXh3hCiacR5vPj1rk-XytrOnpTWPDgZ5Jb8daXjoKLlg==&ch=MhsID6eUGt3_Wp5LTPQvmJ-BLLJczUvhhQsikPZ87k5rRo94j1lyow==>

[https://files.constantcontact.com/ee1208b4001/e25f6bcf-26a8-4e59-91f2-cc248962042d.png]<https://r20.rs6.net/tn.jsp?f=001FWiLaWtlajNk-GsfvFfNmHkusdoIX71fQA8KO93YD4NILTyNsi7KdHWnUyXUCUxmt9lsygrS95gHV8GCSG-HjHs34plgT2OeabPeWCVCwrLQA9sE8vPeg03S9jf6AuLrX0XRTUyd-0Tk9kC-BUSTxDwoHAoIfL3d&c=b4Xw9YcWDajXh3hCiacR5vPj1rk-XytrOnpTWPDgZ5Jb8daXjoKLlg==&ch=MhsID6eUGt3_Wp5LTPQvmJ-BLLJczUvhhQsikPZ87k5rRo94j1lyow==>

Webinars for You

·    April 12: [CANVA] Discover New Features to Enrich Docs and Presentations [3 pm ET for 1 hour]

·    April 26: [PDFs] Create and Edit PDFs Using New Tools and Techniques [3 pm ET for 1 hour]

·    May 17: [MOBILE] How to Organize and Move Photos from Your Phone to Your Computer [3 pm ET for 1 hour]

·    May 24: [WORD] How to Create Gorgeous TABLES in a Word Document [3 pm ET for 1 hour]

View Webinars and Register Here<https://r20.rs6.net/tn.jsp?f=001FWiLaWtlajNk-GsfvFfNmHkusdoIX71fQA8KO93YD4NILTyNsi7KdNyU47q31szvN6KsxDPn8RJv9ym07Oeyah5Mt_O94KyKSDtS9yOtvgOkYgJ-HhSvwMAX-Io07vyaRSpzTrQLaN4tbqbRA6kE6DpQn-7cz5C6&c=b4Xw9YcWDajXh3hCiacR5vPj1rk-XytrOnpTWPDgZ5Jb8daXjoKLlg==&ch=MhsID6eUGt3_Wp5LTPQvmJ-BLLJczUvhhQsikPZ87k5rRo94j1lyow==>

EXCEL/G-Sheets – Split Cell Contents

Advanced

[https://files.constantcontact.com/ee1208b4001/efb64a22-5b49-4c9e-a106-2c09f587d7de.png]

Have you ever had a list of contacts where the first and last names were in the same cell... and you wished they were separated? Maybe you have a spreadsheet or .csv file that you would like to import into a web tool, but with the full name in one column, the data won't display correctly.

What you want, is to have each part of the name in its own column, right?

For instance, if you want to use a mail merge function and address your ezine or solo email to the contact’s first name, you need to split the cell information (the full name) into two cells.

There are other situations like this ... where you have a string of content in one cell that you want to separate -- like a mailing address (including the street, city, state and zip code) -- or a person's title and organization.

Well, in Excel and Google Sheets, you can use the "Split Cell Contents" to quickly put each detail in an individual cell. We'll look at both.

Split Cell Contents in Excel

If your list is in an Excel spreadsheet (or a .csv file you can open with Excel), there is a great shortcut to distribute the contents of one cell into adjacent columns.

[spreadsheet columns]

We'll use the first and last name example where you want to split one column into two – but you can apply this concept to other types of data.

TIP: Before you even begin, watch out for middle names, initials, or prefixes. The addition of these characters in a cell can throw off the separation. It is best to "clean" your data before you begin it if you would only like to have first and last names. Do this by deleting those unneeded details that you don’t want in your final list. If a person has two last names, you can put a dash (-) between them so they are seen as one word.

·    IMPORTANT. First, be sure there is an empty column(s) immediately to the right of the one with the text you are separating so that you won't overwrite data.
·    Highlight the column where both the first and last names are currently.
·    In the Menu, go to the Data section, and click Text to Columns. The Wizard will open in Step 1.
·    In the Original data type, select Delimited and click Next.

[split cell]

·    In Step 2, make sure the Delimiter is set to Space (as in the cell there is a space between the first and last name) and then click Next. TIP: Depending on your data, you may have a Tab, Comma or another type of character between the data you want to separate).

[set the delimeter]

·    In Step 3, you should see the content split into two columns. If you want the separated data to be placed to the right of the original column, click Finish, and you're done.

If you want the new data to be displayed somewhere other than the two columns to the right, you need to tell it which column you would like the first and last names to display.

·    The first column should be highlighted in the Data Preview window. Next to Destination, click the selection box. This will take you into the document where you will highlight the column where you would like the first names to appear.
·    After highlighting the column, click on the data selection box again to return to the Wizard window.

[data selection box]

Split Cells in Google Sheets

Similar to Excel, in Sheets the split feature uses a delimiter (like a comma, period or space) to separate the data in cells.

So again, the data you want to split needs to be consistent. Clean up your content if there are middle names/initials, prefixes, etc…

·    Make sure you have a few empty columns to the right of the column you are splitting.
·    Highlight the cells with the data to be split.
·    Go to Data in the menu and select Split text to columns near the bottom of the menu.

[split text to columns]

[split cell]

·    A Separator button will appear with a dropdown of choices. Select the appropriate delimiter – like a space.

·    The cell contents will be split into two (or more columns) depending on your data.

[https://files.constantcontact.com/ee1208b4001/f8f1ecc8-4884-4c21-8ed0-cf647e6db944.png]

Communications: Writing

Creative Use of "Subheads"

Yes, everyone knows that when you are creating a written document that subheads are useful. We do it automatically when there is a noticeable section that requires identification.

Sooo, the dual purpose of this tip is to first remind you to look for more opportunities to add subheads ... and second, to expand your thinking on what is one.

Subheads give people a chance to breeze through your document more quickly while gaining the key points. They also reinforce your main ideas so that your message gets communicated.

As you look at the screenshot of a technology article below, you will see that we have purposefully created different types of subheads:
1.  Regular, stand-alone section titles
2.  Bold keywords at the beginning of a sentence
3.  Entire introductory phrases

What You Can Do

The cool thing is that you can create your document however you like, then go back and see how you can "pepper" it with creative and engaging subheads. It works!

[https://files.constantcontact.com/ee1208b4001/d08d689f-9c15-43ce-89e3-d4b075d272b9.jpg]

[https://files.constantcontact.com/ee1208b4001/da1c6ee9-ba35-4af5-9c80-8e1b5781189e.png]

Leadership

Show Appreciation

Do you regularly show and communicate appreciation to others… your colleagues, team, friends and family?

Think about the last time you paused and really expressed your gratitude for a project well done. Can you remember when someone went out of their way to do something for you and how you expressed appreciation? What about something more simple such as a colleague following up and doing what they said they were going to do … did you recognize that effort?

Appreciation doesn’t have to be a grand gesture, it can be shown simply with:

·    Your words
·    A pat on the back
·    Eye contact
·    A warm handshake
·    Giving of your time
·    Your showing appreciation lets someone know they have value. It is uplifting.

Real appreciation is not a tactic (something we do so that we can get something back in return). It has to be pure and genuine, from the heart.

Did you know that you can give Tech-Talk to your patrons and students? Schedule a time <https://r20.rs6.net/tn.jsp?f=001FWiLaWtlajNk-GsfvFfNmHkusdoIX71fQA8KO93YD4NILTyNsi7KdLOmEBAvENwKSiEPb9R8YXHh-_pKu_bJhiMX9CgfqwfswnYAU4gRCTiNH1vAGaAc3X0LwztSQE8r5MkvV2E1GVLU4P5qV8MzUaT9Mgv6Yy1KX04xQBl2q64j1I6QtY7G6g==&c=b4Xw9YcWDajXh3hCiacR5vPj1rk-XytrOnpTWPDgZ5Jb8daXjoKLlg==&ch=MhsID6eUGt3_Wp5LTPQvmJ-BLLJczUvhhQsikPZ87k5rRo94j1lyow==> to discover the options you have!

[https://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<https://r20.rs6.net/tn.jsp?f=001FWiLaWtlajNk-GsfvFfNmHkusdoIX71fQA8KO93YD4NILTyNsi7KdGN0clkSOqwEX6mYGW9KC0EbBkVaOSw9k1dRawFtNxdD6yiSGD24ff5lQH9BbIsUpNg8ZwjYCMCDQveAIUW8jXI45VsQ_spdcPC-VJ82L3oy&c=b4Xw9YcWDajXh3hCiacR5vPj1rk-XytrOnpTWPDgZ5Jb8daXjoKLlg==&ch=MhsID6eUGt3_Wp5LTPQvmJ-BLLJczUvhhQsikPZ87k5rRo94j1lyow==>
[Ask a question]<https://r20.rs6.net/tn.jsp?f=001FWiLaWtlajNk-GsfvFfNmHkusdoIX71fQA8KO93YD4NILTyNsi7KdC9k17szHBR0LivjY7kvUl_8URHHYwXrOC_pSPTRzS--uityAb9CPp2DowGmonCCEtApZmQGIo24XXa5L81rDzXCRtN2QkpqfmBPY_zTkI8enpPJhDEaGmE=&c=b4Xw9YcWDajXh3hCiacR5vPj1rk-XytrOnpTWPDgZ5Jb8daXjoKLlg==&ch=MhsID6eUGt3_Wp5LTPQvmJ-BLLJczUvhhQsikPZ87k5rRo94j1lyow==>

Copyright 1996-2023 Shared Results International. Published weekly. Distribution is limited by license. For information on how to include additional recipients, contact support at tech-talk.com<mailto:support at tech-talk.com> 941-355-2092.

Cheers,
Darci Hanning, MLIS (she/her/hers)
Public Library Consultant / CE Coordinator
Continuing Education Resources: https://libguides.osl.state.or.us/conted

State Library of Oregon | Library Support and Development Services
971-375-3491 | darci.hanning at slo.oregon.gov<mailto:darci.hanning at slo.oregon.gov> | www.oregon.gov/library<http://www.oregon.gov/library>
Follow @StateLibraryOR: Facebook<http://fb.me/StateLibraryOR> | Twitter<https://twitter.com/StateLibraryOR> | Instagram<https://www.instagram.com/statelibraryor/> | Pinterest<https://pinterest.com/statelibraryor/> | YouTube<https://www.youtube.com/channel/UC0-kU8Gu0jS_YcnXg-b_TRA/featured>

[cid:image001.png at 01D96D1F.68E23810]          [cid:image002.png at 01D96D1F.68E23810]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20230412/a5928749/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 23473 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20230412/a5928749/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 15548 bytes
Desc: image002.png
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20230412/a5928749/attachment-0003.png>