How to turn two columns in Excel into one without losing contents - concatenate not merge

To be honest I'd assumed the Merge function would sort that out, but no, it creates a single cell from two cells but deletes the content of one and so leaves you with half of your information. Merge is not the function you're looking for.

It turns out that it's Concatenate.

Here's a picture of what I had and what I wanted.

Obviously my first port of call was Twitter.

@JoBrodie make a third column and use concatenate, so basically C = concat(A,B) http://t.co/6MO2aDoZKj
— Lauren G (@geeoharee) October 14, 2014

@JoBrodie = A1 & " " & A2
— Spike (@tenbus_uk) October 14, 2014

I used the second suggestion and it worked, hooray.

How to search across all tabs in an Excel workbook spreadsheet

Tweet the information in this post.


1. Excel sheets
2. Google sheets

1. Excel sheets
Now that I know how to do this I can't believe the time I've spent repeating the process individually, searching within each tab. Shameful.

Thanks to @Richard_Black and @marklardner for explaining this.

When you've got your spreadsheet open and want to look for the existence of a search-string (I was looking for Computer Science or Computing, so used comput as that would find both) do something a bit like the following.

PC users - Ctrl+F brings up the Find menu
Mac users - Command+F brings up the Find menu

1. Ctrl+F or Command+F to bring up the Find menu
It looks like this on my version

2. Click on the Options button and it will look like this- where it says 'Within: Sheet' (the default setting), change this to Workbook. Then you can bounce through the tabs pressing enter (identical to clicking 'Find Next') as you go.

2. Google sheets
Not that different, see picture series below.

Wait for the sheet to load, if you don't and press Ctrl+F you're searching 'in browser' and you want to be searching 'in sheet(s)' so have patience.

Once loaded press Ctrl+F and you'll see a dialog box [1] appear near the top right of your page (if you see one appear bottom left of your browser window [1a] refresh the page and wait patiently, that's the wrong one). Click on the three vertical dots to bring up the options and choose from the options.

 Type in your word or phrase of interest and select the 'This sheet' option [2], to adjust it...

 ...to 'All sheets' [3] so that you search across the whole document, not just the current tab. Once you've entered a search term the 'Find' link will become active, keep clicking it to bounce through the results.

 When it's found everything it can look out for this notice, in [4].

Edit: 30 July 2017
Someone from Portugal was super keen for me to tell you about VLOOKUP. I've no idea what it is (no need either so please don't tell me) but here is Microsoft's own page about it - they wrote the software so I assume they know what they're talking about.

Ways in which data give you away - redaction, and phone keyclicks

1. Redacting information - careful with your spreadsheets and Word documents etc

Earlier today I read the rather interesting story of the unwittingly-released personal data of residents in a London borough, via the MySociety newsletter.

A Freedom of Information request was made to find out who had been provided council housing and the council obliged with information in an Excel spreadsheet. Although sensitive data was not overtly available it was available in 'hidden sheets' which are recoverable by fairly minor geek skills. MySociety links to a page on Microsoft's site that shows how to do this.

While few people downloaded these files (I don't know who downloaded them or what they did with that info) this information should have been redacted.

Redaction involves properly removing something, not just hiding it. A failure to redact or bungled attempts can be unfortunate and amusing and in some cases can draw attention to the fact that an attempt has been made to hide something ;)

Whenever you email a Word document to someone, unless you've removed some of the details the recipient can see when you began editing it, how long you've spent editing it, and the document's author (may or may not be you). Generally this is no big deal and I can't actually think of a time when I've bothered to / needed to do this (the version of Word I have actually prompts you for this, inviting you to make a version more suitable for sharing).

You can also read the much more hyperbolic version of the story from the local paper.

Other posts in the Word tips series...

Redaction and FOI
Paul Bradshaw of Help Me Investigate posted on his blog that any claims made by organisations that redaction will eat into their costs are likely to be nonsense. Apparently there's been a ruling on it: "we find that a public authority cannot include the time cost of redaction when estimating its costs."

2. Mobile phone keyclicks - it's possible to work out what number you're dialling
On the bus this morning someone dialled a number on their mobile and as they had keyclicks on every number entered made that little 'number being entered' sound. This particular person was using the classic two-tone sounds that dial phones make (you can hear each of the tones here, in the DTMF* number keypad bit) - each number has two separate tones combined to form a chord.

*Dual-tone multifrequency signalling

I wondered how easy it would be for someone listening to be able to instantly know which numbers were being entered. Even if someone couldn't do it live a recording of the keyclicks (admittedly it's fairly unlikely that anyone would bother to do this!) could be played back and the number uncovered. While I don't have perfect pitch and could only guess the intervals between the dialled 'notes' I'm sure there are people who could hear the number being dialled live. I've listened to all the tones and I think that some of the numbers 'sound' warmer than some of the others (chords are less dissonant I suppose) but that's about it - I can differentiate them but can't remember which is which.

After I tweeted this idea, @minifig (Thom) replied to suggest that if a recording was made then playing it down the handset of an old style phone would probably be sufficient to ring that number too. After much childhood playing with telephones I know that pressing the button (that indicates when the handset is replaced) several times at different rates could get the phone to do different things so this seems pretty likely - this was later confirmed by tweets from @schrodingerskit and @drjohnmitchell

@JoBrodie @minifig that is correct and is the basis of old-school phone phreaking en.wikipedia.org/wiki/Phreaking
— Schrodinger's Kitten (@schrodingerskit) July 26, 2012

@jobrodie You also used to get phreaks that could get free call by vocalising a tone.
— John Mitchell (@drjohnmitchell) July 26, 2012

Thom also linked to this video of a 2 year old child who can recognise numbers just by hearing them dialled - I suppose it it can't be that rare to be able to do this but I wonder how many people / toddlers have really noticed that the numbers sound different.

 

Funnily enough when I was young my dad gave me a musical calculator which played a unique note (not the dual-tone)  for each number. Although a bit like something out of Close Encounters of the Third Kind I can still remember the 7-note melody that my childhood home phone number made.

Geeky asides aside, this sounds like useful ammunition in trying to get people to switch off the annoying keyclicks on their phone.  I might have to learn the number sounds to be more convincing about this ;)