[Libs-Or] Tech-Talk: EXCEL & G-Sheets - The Cool "Text to Column" Option

Thu Mar 7 11:06:40 PST 2019

Greetings and welcome to this week’s issue of Tech-Talk!
Using other delimiters in the "text to columns" tool
[http://r20.rs6.net/on.jsp?ca=375b6877-eb11-4154-b88f-ecd8484e6fce&a=1100778316270&c=980e0ed0-0713-11e6-a7b7-d4ae528ed502&ch=981cb4d0-0713-11e6-a7b7-d4ae528ed502]

[http://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png]

 This Week's Topic: EXCEL & Google Sheets
1. VIDEO ... The Cool "Text to Column" Option
2. ARTICLE ... The Cool "Text to Column" Option
3. COMMUNICATING ... Group Calls ... Who Are You?

[Text to Columns]<http://r20.rs6.net/tn.jsp?f=001-FrjStahjEglNkhpltCvL7es0Rb_IBLKAD6XSDoP7bmf57CJmoxKjRZwsaMPK96B_ZsV9bDCBZiT1rhf_R863jp9uYhvrnZ0Pb4ntw74DjTZ1zMEpYA1rEA6KXDf-bD4Rjarw3Zi2-Wk8Sh8iZ_HADQ4vyJgQWouJU39kCDww9wa_WRw1ZZfGLJxq-6COWy3zXRwDCTCS24=&c=geKh3mvCX8EajD3eoyvRYS0E-tSccNltUr7oFEcbAknCvt9cjKVjWA==&ch=WXe0zoksJ5L_5FqaRcCpIsgv9YR1aB67kkyGVDZMteLTTsUm-Vy68w==>

Photo by Derek Oyen<http://r20.rs6.net/tn.jsp?f=001-FrjStahjEglNkhpltCvL7es0Rb_IBLKAD6XSDoP7bmf57CJmoxKjRZwsaMPK96BxwUrk3OJgTBetPx_jXDRU61Z9KW3QTjQ4qQYwg9ZQ8btz6e56i4Xl2m3klvR0ntZM3wJt7p26dzwbMehJV_lO0YCtRg9DJtyRQfzOkMa-es1rehghqRP7iIg3YBR1CwOJmk_7EscNWk91HZSzIvJZqxZWCFOo0qK80iReYcVQotw2eFkhnsW3ykcVMUlm83XPRq4I7musf7ZtkK5ISVmUw==&c=geKh3mvCX8EajD3eoyvRYS0E-tSccNltUr7oFEcbAknCvt9cjKVjWA==&ch=WXe0zoksJ5L_5FqaRcCpIsgv9YR1aB67kkyGVDZMteLTTsUm-Vy68w==> on Unsplash<http://r20.rs6.net/tn.jsp?f=001-FrjStahjEglNkhpltCvL7es0Rb_IBLKAD6XSDoP7bmf57CJmoxKjRZwsaMPK96B9_FkrxB721lQZOJ791hPhnrECsEVxbK4CZgzkoBGOLwJB-uXF5P95J27qustkSMTgpCTlKJmLZoHznQJ0sKcDAixwf1BLgtE0v8bpfcsVmEcOHGowljQm7TZnIH9D0S5E8yLEM3-zc-3edrcLbHIPcvdyvbUpMXmQsQeinqw3VQliP3qkgpl2GkUj9SzhTAedefgN-pGFEg=&c=geKh3mvCX8EajD3eoyvRYS0E-tSccNltUr7oFEcbAknCvt9cjKVjWA==&ch=WXe0zoksJ5L_5FqaRcCpIsgv9YR1aB67kkyGVDZMteLTTsUm-Vy68w==>

Brought To You By State Library of Oregon

[State of Oregon]

Visit Tech-Talk.com Archives<http://r20.rs6.net/tn.jsp?f=001-FrjStahjEglNkhpltCvL7es0Rb_IBLKAD6XSDoP7bmf57CJmoxKjRp90uTtFCcLEbd1M--r0AahkRDIvqlw6C9XVUOFwWYCp6h1PEgJgVfpxFHozTtNqlRtr_Ron_uXaTwa8p7vqk1LuUjhhpcNM88EeuSo16R_&c=geKh3mvCX8EajD3eoyvRYS0E-tSccNltUr7oFEcbAknCvt9cjKVjWA==&ch=WXe0zoksJ5L_5FqaRcCpIsgv9YR1aB67kkyGVDZMteLTTsUm-Vy68w==>

Username: ORLIBTECH
Password: ORLIBTECH

Questions about Tech-Talk?
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>

EXCEL & Google Sheets - The Cool "Text to Column" Option
Advanced

Picture this: You have a spreadsheet with data, but one of the columns has multiple types of information in it. For instance, both the first and last names are in the same cell. Or the name, address and phone number are all together in one. You want to split them out so that each element is in it's own column.

That's where the "text to column" feature comes in handy! It's so cool!

In our example below (a downloaded chat report from a recent GO Tech-Talk Call), three elements are all scrunched together in one column: 1) the word "from", 2) the person's name, and 3) the ideas they typed into the chat field.

We wanted to easily get to the chat text alone because there were ideas for how to get students and patrons excited about the new Tech-Talk Database.

Sure, we could manually copy and paste part of each line into a Word document, but that would be tedious! Plus, we noticed that every entry had a colon (:) separating the name from the content, making it especially easy to use the "text to column" feature in Excel (and the "split text to columns" in Google Sheets)!

TIP: If your data is in a Notepad (a format often used when downloading data), you can simply copy the entire contents (CTRL + A) and paste it into an Excel or Google spreadsheet first.

[https://files.constantcontact.com/ee1208b4001/b0217b7f-9a98-4b26-9a78-7e9bc109bf04.png]

You can run into this type of opportunity when you least expect it. In our case it was information showing up in Notepad. Other times it may already be in a .csv or .xls file.

In any case, the "Text to Columns" feature is handy to know ... especially when the delimiter (such as the colon [:]) is not one of the choices.

NOTE: Delimiter is the symbol or character(s) that separates types of information. In our example, the name and the chat text.

TIP: To learn more about the basics of the Text to Column feature, review an earlier article called Splitting Cells<http://r20.rs6.net/tn.jsp?f=001-FrjStahjEglNkhpltCvL7es0Rb_IBLKAD6XSDoP7bmf57CJmoxKjRZwsaMPK96BC3wZwr0j3KmMtVd3zby78QYMmEcU3vG5lEErK7yXq36gSpBF1VbsQ7BGsZlE1SK85_wtaWCGIpaFNjlgq6-3AjnR0QDw7EQd2gIt7qUxntsVhup9zdTvL2PElDW2sfSc&c=geKh3mvCX8EajD3eoyvRYS0E-tSccNltUr7oFEcbAknCvt9cjKVjWA==&ch=WXe0zoksJ5L_5FqaRcCpIsgv9YR1aB67kkyGVDZMteLTTsUm-Vy68w==>. It introduces you to the concept and shows you the frequently used delimiters.

The Delimited Data in "Text to Columns"

When your data is "Delimited" (meaning that the content is separated by characters such as a comma, semicolon, tab or space), you can easily convert the text to multiple columns using these elements (the delimiters). They act as a sort of cue to tell Excel or Google Sheets to move content to the next column.

However, sometimes your text doesn't have one of the standard delimiters, but another character. This was the case with our chat box data. The content wasn't separated by the normal tab, space or comma, but by a colon.

So... when there is a clear symbol (or character) that is in each line of data separating the content in it, you can use the symbol as the delimiter.

[https://files.constantcontact.com/ee1208b4001/a0fbe18b-857c-4d38-9a86-151ff360ce2c.png]

How to Use an "Other" Delimiter in Excel

Instead of manually copying each line of data and separating it into columns, when you have some type of delimiter, you can use the Text to Column feature. You "tell" Excel what character to use for separation.

In our example, we'll use a colon as the delimiter using the "other" option.

·    To begin, in Excel, highlight the column where the data is currently.

·    Go to the Data Tab and in the Data Tools section, click on Text to Columns.

[text to column]

·    In Step 1, choose Delimited, then click Next.

·    In Step 2, in the Delimiters box, check the "Other" box and type in your character. In this example, it is a colon.

[delimited]

·    In the Data preview section at the bottom of the box you'll see what your data will look like separated by the special character. If this is acceptable, click Next.

·    In Step 3 you are able to select the type of data format for each column. If it is all text, you can select General or Text.

·    Click Finish to view your newly separated data.

How to Use an "Other" Delimiter in Google Sheets

[split column]

Just as in Excel, Sheets has the option to choose an "other" character delimiter when separating text to columns.

·    In your Google Sheet, highlight the column with the data you would like to separate.

·    In the toolbar, go to Data, and select Split text to columns.

·    Now, Google Sheets is very intuitive. If it detects a delimiter it will separate the data without you making a selection.

[custom]

·    But if you have a special character, as we have in this example (a colon), we can choose the Separator option and select Custom.

[separator]

·    The Custom box will open where you can type in the special character.

·    Your data will immediately be separated into columns in your Sheet based on this delimiter.

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

Communication: Conference Calls
Identify yourself

Have you ever been on a conference call (the type where anyone can chime in) and wondered who was talking?

Sometimes there are even two or more people on the same computer, all using the one microphone (and not appearing on camera) ... so you don't know who is sharing an idea or commenting on yours.

Conference calls are the name of the game in today's work world, so the more effective we can make the communications, the more productive the session will be.

Two Techniques

1. Say your name first. Each time you start talking, say "This is Sue" or something similar. People will appreciate it. When you can't see someone, recognizing the individual by voice isn't always easy.

This next one is a little tricky. How do you feel about it?

2. Tell the group you've joined the call. No one can see you when you "walk" into a virtual meeting room. If you don't speak up and say "I'm here" the others may not realize it.

I personally like it when someone says (at a good opportunity), "I'm on the call, it's Jeremy."

Of course the appropriateness of this technique varies by type of session and whether or not you've joined late.

When everyone can see who else if in the room, it's not necessary to announce. But when you've come in late, it's polite in my opinion, to let people know you're there.

What do you think? Any ideas for making this idea work? Share your thoughts with us. (Use the member login info below.)

When You Click on the Images Below, Use This Login<http://r20.rs6.net/tn.jsp?f=001-FrjStahjEglNkhpltCvL7es0Rb_IBLKAD6XSDoP7bmf57CJmoxKjRp90uTtFCcLEbd1M--r0AahkRDIvqlw6C9XVUOFwWYCp6h1PEgJgVfpxFHozTtNqlRtr_Ron_uXaTwa8p7vqk1LuUjhhpcNM88EeuSo16R_&c=geKh3mvCX8EajD3eoyvRYS0E-tSccNltUr7oFEcbAknCvt9cjKVjWA==&ch=WXe0zoksJ5L_5FqaRcCpIsgv9YR1aB67kkyGVDZMteLTTsUm-Vy68w==>...

Your Username: ORLIBTECH
Your Password: ORLIBTECH

[http://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<http://r20.rs6.net/tn.jsp?f=001-FrjStahjEglNkhpltCvL7es0Rb_IBLKAD6XSDoP7bmf57CJmoxKjWzTPoER3fGHYXnpQGWNk0J36A2aZwQtkXMxojdJKAJG27h6GC8qRaWWk68i5Q5SrES93muPbX8Xc94k7CfpLQWUbJ0l0XtECIAIRovpXPZp&c=geKh3mvCX8EajD3eoyvRYS0E-tSccNltUr7oFEcbAknCvt9cjKVjWA==&ch=WXe0zoksJ5L_5FqaRcCpIsgv9YR1aB67kkyGVDZMteLTTsUm-Vy68w==>

[Ask a question]<http://r20.rs6.net/tn.jsp?f=001-FrjStahjEglNkhpltCvL7es0Rb_IBLKAD6XSDoP7bmf57CJmoxKjcUEydz9k21kmOd46w8lY-vw4HRbCTkzejPfhFnukn7BpeGsegXHtejwj9kAZYvPL9NI01EiFF5WvVSIRLlN5Pi7ejY-MJ83yC3weg7peofB2iUUKFR_zME=&c=geKh3mvCX8EajD3eoyvRYS0E-tSccNltUr7oFEcbAknCvt9cjKVjWA==&ch=WXe0zoksJ5L_5FqaRcCpIsgv9YR1aB67kkyGVDZMteLTTsUm-Vy68w==>

[https://files.constantcontact.com/ee1208b4001/ed999f41-b756-48df-8d2b-9387299a238b.gif]

The TECH-TALK DATABASE is now available for patrons and students ... an additional licence option!

We frequently get requests from librarians asking if they can share these tips with all their colleagues ... staff, faculty, patrons ... and of course students. So, we created a solution to consider.

If you you'd like to know more, contact us.  You can: 1. Reach us by phone (941-355-2092), 2. Send us an email (Linda at Tech-Talk.com<mailto:Linda at Tech-Talk.com>) or 3. Schedule a call now<http://r20.rs6.net/tn.jsp?f=001-FrjStahjEglNkhpltCvL7es0Rb_IBLKAD6XSDoP7bmf57CJmoxKjRlT92JnAF0I7Muck86w3awm4kuN5W_3vCPGweKNmeDicC-5YAbzNfwNbpK6TGhUMslrBNoLiHQJAgdRJil_RnGCTRL8U0DSBW3YXTmjwqWxNOUXVWZz1SgSPSbcR4G-yUJN_lLgBKniy54j18xHxQ4=&c=geKh3mvCX8EajD3eoyvRYS0E-tSccNltUr7oFEcbAknCvt9cjKVjWA==&ch=WXe0zoksJ5L_5FqaRcCpIsgv9YR1aB67kkyGVDZMteLTTsUm-Vy68w==>.

Copyright 1996-2019 Shared Results International. Published weekly. Do not forward this issue of Tech-Talk.com without prior permission from Shared Results International. Distribution is limited by contract. Forwarding it to unauthorized recipients constitutes copyright infringement.
For information on site licenses or how to include additional recipients, email darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>

The Tech-Talk e-newsletter for Oregon library staff is distributed weekly via the Libs-OR and TechTalk mailing lists. Please do not forward. Know someone who might be interested in receiving a copy but they’re not on Libs-OR? Have them contact darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> or visit http://listsmart.osl.state.or.us/mailman/listinfo/techtalk to subscribe to just the Tech-Talk newsletter!

This paid subscription service 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.

Cheers,
Darci Hanning, MLIS
Public Library Consultant / CE Coordinator
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> | 503-378-2527| www.oregon.gov/library<https://www.oregon.gov/library>
Follow us: Facebook<http://fb.me/StateLibraryOR> | Twitter<https://twitter.com/StateLibraryOR> | Tumblr<http://www.statelibraryor.tumblr.com/> | Pinterest<https://pinterest.com/statelibraryor/>

[cid:image005.png at 01D31C27.4B9FEA30]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20190307/0cf609d4/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20190307/0cf609d4/attachment.png>