[Libs-Or] Tech-Talk: EXCEL/G-Sheets - Combine Multiple Cells into One Without Losing Data

Tue Jun 11 13:32:56 PDT 2019

Having problems viewing this newsletter? View in your web browser<https://www.tech-talk.com/combine-multiple-cells-into-one.html> instead – when prompted for the USERNAME and PASSWORD use: ORLIBTECH for BOTH.

You can combine cell contents with this formula
[http://r20.rs6.net/on.jsp?ca=ee73f05a-80c6-4a15-8212-16be5ccc1ce8&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 ... Combine Multiple Cells into One Without Losing Data
2. ARTICLE ... Combine Multiple Cells into One Without Losing Data
3. COMMUNICATING ... Is it "Me"or "I"?

[combine cells]<http://r20.rs6.net/tn.jsp?f=001tgZhYNSz74C-XFe1kAYKhlflMItu5Zr-mbjjn6WlTlpemFfxGaFzUe9Axp0Z7U3pLDc_8VISeha7yjF1VQiE0KmD-V1m_p25Qhj0WEa06xPwDuQ6WLzDtkl8UeBFPWGQp3Q0wSFJBXG_Sg2H1C2AI5aOL584DTr9rfes4l0sb8plov9nvc9IltiSLztHvpQC&c=cafGHhLbcVQmwOWDqvGES2mxBiZ7CE2v5thcK8iNfpN47_A6j-Qjtw==&ch=hdWmWuUWB-cKnwUh_FbLFEGulhFdtPuE9ED368RyUDblOojGr66R5w==>

Photo by Park Troopers<http://r20.rs6.net/tn.jsp?f=001tgZhYNSz74C-XFe1kAYKhlflMItu5Zr-mbjjn6WlTlpemFfxGaFzUe9Axp0Z7U3pyYk9Sdj4txDoRI2kdbivlq_h3ojt3Slhs4do1ZyW78yx7Ow9iFH3XRAxOVFlvOrUl7kPFSk_sFC2APZml6Mnvf36wmlV4OuFINTs24ZAjVytg7gPfR8O29HSBTNndwcJcqf16sE9YVOLXnQ-qtl07bulqLggbe-T6cJORD5eAdf4rIr8Mt2Ipo0qXwBu2DeDnT9xlHr6XS4=&c=cafGHhLbcVQmwOWDqvGES2mxBiZ7CE2v5thcK8iNfpN47_A6j-Qjtw==&ch=hdWmWuUWB-cKnwUh_FbLFEGulhFdtPuE9ED368RyUDblOojGr66R5w==> on Unsplash<http://r20.rs6.net/tn.jsp?f=001tgZhYNSz74C-XFe1kAYKhlflMItu5Zr-mbjjn6WlTlpemFfxGaFzUXnrirnEALpRgCfZgqyYuY6JiDWJnFbijgojpL7QKCuNBF3D1KQuE6CCtsEKTP6Qcox5Iwt7EtJW8_s4qELbFN2uAz6dXRLg9YX7B2jSQM1l4O1nNJNSovjNLBXI10_wRf1M1S2ikWH17tG9yXsTWXIHhhG45w0BbuEwakgZ1EPDt6elq9dv_rSj3nc9P4VwdT0uisZjLd91VH7KhgJP74nlXZ32IsQ5Fg==&c=cafGHhLbcVQmwOWDqvGES2mxBiZ7CE2v5thcK8iNfpN47_A6j-Qjtw==&ch=hdWmWuUWB-cKnwUh_FbLFEGulhFdtPuE9ED368RyUDblOojGr66R5w==>

Brought To You By State Library of Oregon

[State of Oregon]

Visit Tech-Talk.com Database<http://r20.rs6.net/tn.jsp?f=001tgZhYNSz74C-XFe1kAYKhlflMItu5Zr-mbjjn6WlTlpemFfxGaFzUVGxSeOxBkDTY9xEM2WYMUEnLUpWI9V1sFc-TDVTHotCYsiUVku27fms4bmt2IzfYkQBIcEXPOuy3YtvDKJ7I316KxUVd_-fLVX1pvEeHYtQ&c=cafGHhLbcVQmwOWDqvGES2mxBiZ7CE2v5thcK8iNfpN47_A6j-Qjtw==&ch=hdWmWuUWB-cKnwUh_FbLFEGulhFdtPuE9ED368RyUDblOojGr66R5w==>

Username: ORLIBTECH
Password: ORLIBTECH

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

EXCEL / Google Sheets: Combine Multiple Cells into One
Advanced

[combine cells]

The other day I was given a spreadsheet of names with the first name in one column and the last name in another.

However, I wanted both the first and last names in one cell, together. (It's funny because normally the opposite would be true. I usually find myself splitting the contents of a cell<http://r20.rs6.net/tn.jsp?f=001tgZhYNSz74C-XFe1kAYKhlflMItu5Zr-mbjjn6WlTlpemFfxGaFzUX5TSe7w7TAKH4p-LuWAykC6ku41yCEZ8ya8G97WpGbCZ2RZRxp226X5dP8ARlS2NzClg5VBjCNXT1oEeP-Wj1AieSMy1y0nDCsnrAXrhrZ3JMeicLhK6oYSLS-VhXCq6yKFIL_s9IOj&c=cafGHhLbcVQmwOWDqvGES2mxBiZ7CE2v5thcK8iNfpN47_A6j-Qjtw==&ch=hdWmWuUWB-cKnwUh_FbLFEGulhFdtPuE9ED368RyUDblOojGr66R5w==>... not putting them together.)

But instead of retyping all the information manually, I knew there must be a way to do this easily. Maybe I could just merge the cells? Nooooo...

Why Not Use "Merge and Center"?

[merge cells]

If you work in Excel (or Google Sheets) regularly you might think that there's an easy way to combine cells using the Merge & Center<http://r20.rs6.net/tn.jsp?f=001tgZhYNSz74C-XFe1kAYKhlflMItu5Zr-mbjjn6WlTlpemFfxGaFzUe9Axp0Z7U3p5sNIO7NINgTEsF7RzBo4LOqI52mnQfyMbn7bRE8HAMWOHFy4113P29rGv67FoM1S0D-nONYZoFJp7QwYUezqQwmUvxWV3oFePeWPtF2D-yFKZest5-5Y6RZ5j0eACbvD&c=cafGHhLbcVQmwOWDqvGES2mxBiZ7CE2v5thcK8iNfpN47_A6j-Qjtw==&ch=hdWmWuUWB-cKnwUh_FbLFEGulhFdtPuE9ED368RyUDblOojGr66R5w==> button in Excel (or Merge in Google Sheets).

However, that only works if you want to merge one or more cells where only one of the cells has content in it.

This is because the feature collapses the cells to make them into one large one. But if you have content in more than one it only keeps the text or numbers of the upper left cell and deletes the rest.

So how do you combine the contents from two (or more) cells into one? There's a special formula to use. At first it looks a little bit complicated, but if you break it down it makes more sense and is easy to apply.

Good news: the same formula works in both Excel and Google Sheets!

Combine Two Cells into One

The secret of this formula is the Ampersand (&) symbol.

Let's first walk through an example, then break the formula down so that it is more understandable. Example: We have an Excel spreadsheet where we need to combine the first name and the last name into one cell with a space between the text in the cells -- as in the image below.

[combine cells]

The formula we're going to use for this looks like this (of course the column letter and row number will vary with your spreadsheet):

=A2&" "&B2
Translation:
·     All formulas start with an equals sign (=)
·     The cells that you want to put together are indicated by a letter and a number (A2, B2)
·     Quotation marks are used at both ends of text, and in this case the "text"is an empty space. ( ). You want a space between the first and last name, rather than having them squished together.

That leaves the & (ampersand) which means "and". So the tricky part in this formula, is to remember to use the ampersand twice - before and after the quotation marks.

Step by Step Instructions
·     Click on an empty cell to select it where you want to add the combined data.
·     Type =
·     Put your cursor in the first cell you want to combine and press enter
·     Type &
·     Add a quotation mark, then a space, then another quotation mark like this: " " (this puts a space between the content of each cell)
·     Type &
·     Select the next cell you want to add and press enter.
·     Your formula will look something like this =A2&" "&B2 (depending on what column letters and row numbers you're using).

[combine cells]

And the results of the formula looks like this:

[formula results]

Again, this same formula also works in Google Sheets.

Let's Summarize the Parts of the Formula

=A2&" "&B2

[break down formula]

Merge the Contents of More Than Two Cells

You can combine more than two cells by continuing the formula. Here's an example to combine the contents for four cells.

Formula: =A2&" "&B2&" "&C2&" "&D2

Note that you need to add the "&" (AND) symbol between each part of the formula. So it reads kind of like:

= cell 1, AND a space, AND cell 2, AND a space,
AND cell 3, AND a space, AND cell 4

[https://files.constantcontact.com/ee1208b4001/b86f0b20-3314-43b0-a2cb-788ee4f915df.png]

Now Apply the Formula to The Rest of the Rows

[drag formula down]

Now you can apply that formula to the other entries in the column (or row).

·     Click in the cell that has the new formula
·     Hover over the lower right corner until the + sign shows
·     Drag the cursor down the column to apply the formula to additional rows

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

Communication: Grammar
Is it "Me"or "I"?

Have you ever been confused about when to use "me" or "I"?

For instance, which of these sounds better to you? Which one do you think is grammatically correct?

1. When you get done with that special report, will you send it to Julie and I?

2. When you get done with that special report, will you send it to Julie and me?

Sentence #1 is actually wrong ... as proper as it may sound.
Sentence #2 is correct.

Here's a trick to use.

Try taking Julie out of that sentence. When you do that, it sounds weird, right? You would never ask someone to send something to "I" when he or she is done.

The reason it sounds weird is because "I" is the object of that sentence -- and "I" should not be used as an object. For objects of action, you'd use "me."

[http://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<http://r20.rs6.net/tn.jsp?f=001tgZhYNSz74C-XFe1kAYKhlflMItu5Zr-mbjjn6WlTlpemFfxGaFzUc3ck8wmuZjZJL2Vf_vJKgRFKYK4TNZ6ga6i-Es116wi7dFd9Z9Yu3xscMFF98IvjqFL4-Pto73mLjAeUPPTouBfjxaTMaLunQ5PYcmwskQ6&c=cafGHhLbcVQmwOWDqvGES2mxBiZ7CE2v5thcK8iNfpN47_A6j-Qjtw==&ch=hdWmWuUWB-cKnwUh_FbLFEGulhFdtPuE9ED368RyUDblOojGr66R5w==>

[Ask a question]<http://r20.rs6.net/tn.jsp?f=001tgZhYNSz74C-XFe1kAYKhlflMItu5Zr-mbjjn6WlTlpemFfxGaFzUfMAuKaI97dSxvkRpDd9Cm-oF2NQNNbGXNmsQBy3REcbWjYXJofoEmk8bqWv6ozF8UdujfV3x2stDPvyq1D8NS17YyrppKIC-_DtzuMys1dU_mxr811wm5A=&c=cafGHhLbcVQmwOWDqvGES2mxBiZ7CE2v5thcK8iNfpN47_A6j-Qjtw==&ch=hdWmWuUWB-cKnwUh_FbLFEGulhFdtPuE9ED368RyUDblOojGr66R5w==>

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/20190611/6673a1a7/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/20190611/6673a1a7/attachment.png>