On a recent data import between Xero and OnePageCRM I needed to turn multiple column address into an address list into a single line. For this I needed the PHP implode function so I wrote one for Excel.
The data was structured like this:
And I needed this:
My immediate faction was to use the implode function that I use in PHP everyday but it appears to be missing in Excel so I quickly wrote one.
To add this code you need to do the following:
Open your work book
Press ALT + F11 and the code window should appear
Right click on VBA project and choose INSERT then MODULE
Copy paste the following code:
Function IMPLODE(Rng As Range, Sep As String)
Dim TEMP As String
For Each Cell In Rng
If Cell.Value = "" Then
TEMP = TEMP & Cell.Value & Sep
TEMP = Left(TEMP, Len(TEMP) - Len(Sep))
IMPLODE = TEMP
Now in your worksheet go to the cell you want the combined field to be added to and type
and Excel will magically merge the 3 columns A1, A2, A3 together and separate them using the separator you defined.
Many months ago I purchased an HP Photosmart 5520 Wireless printer/scanner to use in the home office. Initially it ran very well but as we were doing some work on the house I simply plugged it in whenever I happened to need it. Once the building work had finished the printer was installed in a more permanent position and the problems started to reveal themselves.
Quite often you would be able to print then 30 minutes later the printer would have disappeared from the network resulting in a reboot of both the printer and the Homehub. Mostly this solved the issue but seriously dented the “convenience” of a wireless printer.
I initially tried playing around with the printers own settings to no avail. After this I went to the HP forums to see if anyone else had a similar problem but was left feeling largely underwhlemed by the support experience which generally gave people a huge list of things to try that would have no bearing at all on the issues they were experiencing (With 20 years of IT support experience I feel qualified to make this statement). So the search began in earnest to try and find a solution myself.
My company, Pentangle, have been providing IT support to lots of companies for many years and we’d never been fully happy with any of the DSL routers in the marketplace. Even expensive kit like Cisco had it’s foibles so we set about building something to our own specs. Plenty of Googling later and someone far more technical then myself had built us a Tomato based router on an ASUS RT-16n box. Coupled with lots of fancy QoS rules it was ready to replace my BT Homehub.
Initially I setup the Tomato at home to test the performance benefits of the device over the BT home hub but had no idea that it was about to solve my printing headache! And Lo and behold the HP Photosmart 5520 has now started working faultlessly.
So, the solution it seems is to ditch the BT Homehub in favour of a different router. We’re testing out some NETGEAR equipment at the moment and hope to report back on that but if, in the meantime, you are desperate give Pentangle a call and order one of our routers!
PS I’ve been told this happens on the latest BT Homehub 4 too…
I recently found my Apple TV to not be appearing in my iTunes meaning I could not stream video to it or access any content on my computer using Home Sharing
The fix is incredibly simple it turns out after hours of Googling and changing settings.
Simply do the following:
1. Go to “http://bthomehub.home”:http://bthomehub.home in your web browser of choice.
2. Go to settings and enter your password.
3. Now go to wireless and select WPA or WPA2 only as your security setting.
4. Do a restart of all devices just to be safe and to make sure everything is saved.
You should now be able to see your Apple TV once again!
I have a confession to make. I have never until the day of this event driven a Lamborghini of any description. As a petrolhead I feel this is a massive, massive oversight on my part and I feel a sense of guilt about even calling myself a petrol head now this news is public although perhaps the story of the day I drove at over 200mph might appease some of you…
As has already been well documented on these pages, a few years back I was lucky enough to win a car from BOTB. This year they gave away their 200th car and as part of the 200th celebrations I was invited to head to Bruntingthorpe to spend the day with the BOTB team, a few journalists and another previous winner.
The day was being run by Ecurie25 on BOTB’s behalf and our first stop was of course a briefing. Until this point I don’t think I’d considered the implications of travelling at 200mph in a car I’d never driven but between the instructors they made sure we were aware of the gravity of the situation! To put it in simple terms 200mph is roughly 90 metres per second!
The format of the event was simple, take a lesser Lamborghini round the track to see how it feels, learn the track and prove to the instructor you are capable of finding the brake when told to. When I say lesser Lamborghini it was still a LP570-4 Performante which can only be called ‘lesser’ when in the presence of a car like the Aventador we were to try and break the 200 mph barrier in.
After the sighting lap it was time to jump into a black Ferrari 458 ready to try and hit around 180mph…
184 mph later and the instructor seemed reasonably happy I wasn’t going to get him killed on the 200 mph run.
So a bit of waiting around and chat with a chap called Tim who turns out to be quite famous in the world of YouTube and cars ( Shmee150 ) and the orange beast pulled back into the pit area.
At this point I was suddenly filled with dread. I was up 10th out of 12 drivers and all 9 before me had reached 200 mph. It suddenly dawned on me that the tiniest mistake could leave me being “the guy who did 199mph”. This REALLY couldn’t happen! A deep breath and it was time to get going…
So, success. Sadly not the 203 mph the instructor called but a highly respectable 201 mph. I’d done it!
Finally the last 2 drivers did their runs and we all congregated to take the obligatory press shots ( we could hardly refuse!) and for a small prize giving where Will and Rupert gave us each an orange Aventador model with a 200mph plaque on it.
Sometimes something comes along that you feel you need to get involved with. When my good friend Peter told me of his plans to enter the Scally Rally this year I was interested, when he told me about the reasons for doing it I volunteered on the spot.
So this year I’ll be painting myself green and strapping myself into the driving seat of a modified SAAB 93 to take on the 1400 mile challenge…
Here’s a bit about what we are doing and why…
h2. An Invitation to Support Clic Sargent & Oliver Lee
We are writing to invite you to join the stars of Toy Story; Woody, Buzz, Mr Potato Head and Sarge the Green Army Man, as they travel 1400 miles in their good
friend RC Racer (previously known as a Saab 93 convertible before we got creative…).
h3. The Background
When we heard that a friends child had been diagnosed with cancer, shortly before his second birthday, words simply felt inadequate. You wonder where the children and the families get the strength to fight this disease and you realise that the answer is simple – because they have no choice. We decided that we should try and bring some smiles & laughter to their days with the Toy Story theme, as well as raising money for the charity supporting them, Clic Sargent.
CLIC Sargent is the UK’s leading cancer charity for children and young people, and their families, providing clinical, practical and emotional support to help them cope with cancer and get the most out of life. For more information, please visit “www.clicsargent.org.uk”:http://www.clicsargent.org.uk
Oliver Lee, the inspiration and drive for Team Ollie, has Rhabdomyosarcoma, a rare cancer affecting just 60 children a year. Oliver had a 5.5cm tumour on his bladder and secondary tumours on his lungs and pelvic bone, and had to be catheterised as he could not urinate due to the size of his tumour. At his scan in January 2012 all secondary tumours had gone and the primary tumour was down to 1cm.
This has taken 4 rounds of Chemo, causing hair and skin loss, but this little boy keeps smiling! Oliver continues treatment and is doing well and we hope his progress continues to be positive.
h3. The Event
The car will travel on a “www.scallyrally.co.uk”:http://www.scallyrally.co.uk event, from Caernarfon- Lytham St Annes-Dumfries-Oban-Fort William. The event is NOT a race, more of a treasure hunt on wheels!
The event ends in Fort William. Our team will then travel to Nottingham, to the Queens Medical Centre. We will be visiting Oliver and his family and hope that as many children as possible being treated in E38, the main treatment ward at QMC will get to see RC Racer.
h3. How can you help?
You can donate to our cause online here: “http://www.justgiving.com/teamolliercracer “:http://www.justgiving.com/teamolliercracer
You can send a text to 70070 with the text OLLI55 (that’s a letter I in there) followed by the amount you want to donate e.g. to donate £5 to our cause text “OLLI55 £5” to 70070
Or you could sponsor us! The details are here in a PDF for you to read (or show to your boss!) >>> RC Racer Sponsorship.pdf
Even if you can only spare £1 it’ll get us £1 closer to our target of £3,000.
Having had a wonderful week away with my family I returned home to an email from BOTB.com with the subject title “Best of the Best/DC”. To begin with I thought it was an announcement from BOTB.com about competition or some big news of some kind but once I opened it my week managed to get even better…
The opening lines of the email were exciting enough, as I’ve become pretty good friends with the BOTB.com team, I did after all win a car from them and throughout the process of choosing, doing photo shoots for my winners profile etc we got to spend a fair bit of time together:
“Hope you’re well and still enjoying the Cayman! How’s it running? Things good here at BotB – in fact, we’ve signed up David Coulthard to be a brand ambassador which is why I’m getting in touch…”
The next part of the email basically laid out a plan for David Coulthard to do a photo/video shoot with them and if there was enough time to get him to interview me about my win, after all I’m sure there are still a whole lot of people who think no one ever wins the competitions, even I was always slighty unconvinced until I got the phone call!
As you can imagine I took a long while to decide wether to take part or not, it was at least 2-3 seconds before I called Ben and agreed a date and time to come along.
At this point it struck me that I didn’t have my car any more, for lots of reasons I’d decided to move on (mostly as I’m saving for something else…) so I set about using my network of friends and acquaintances to procure something a bit more exclusive than my 12 year old Lotus Elise with all the battle scars it’s gained in it’s cherished but hard worked 68,000 miles. A keen fan of Lotus cars (I’ve had 7 of them!) I dropped a message to Lotus Cars themselves and to Parky at Lipscomb Lotus to see if I could be cheeky and borrow a car somewhere and maybe even get it into a few pictures. Surprisingly I got a resounding “YES” back from Parky and set about organising things.
In no time at all the day of the event arrived. Every day for the previous week had been clear skies and sunshine, this particular morning was anything but that. In the gloom and rain I set off from Oxfordshire to Kent to collect an Evora S.
Sadly Parky was ill that day so I still haven’t managed to meet up with him (and I owe him BIG TIME!) but one of the sales staff sorted the car for me and got me on my way towards the location of the photoshoot.
Obviously it would have been daft to borrow such a great car and NOT take a quick diversion to some twisty roads to see what the Evora S was capable of so I set off in search of some! The car felt like a grown up Elise, great steering, plenty of power and a nimbleness that only a Lotus can achieve. Grip was good and the feedback from the chassis enabled me to make swift progress. As a driving machine it’s really up there. OK it doesn’t deliver the raw figures that other manufacturers band about but the figures it has it uses together to create a car which, apart from a few interior tweaks and the frankly awful Pioneer stereo/sat nav (think cheap android tablet/think easily replaced), really is fantastic as both a motorway cruiser and a b-road hero. I’m definitely going to be looking hard at these when the time comes to buy my next car.
Anyway, I digress…
I arrived at the location of the shoot in what can only be described as a monsoon. Chaos reined as photographers, car delivery teams and BOTB.com staff ran from car to cover interspersed with fresh towels appearing to dry the cars in the few moments where the rain abated. With DC booked to arrive at 2pm things weren’t looking good…
Despite the rain attempting to scupper the days plan Will was still his usual cheery welcoming self and after getting me to park the Evora amongst a bevy of desirable metal I was led in to the house for a cup of coffee and some food.
At this point there were a multitude of lighting people, photographers, cameramen, a make up artist and BOTB.com team all working their way through the days shooting plan with only an iPhone weather app to guide them! Given the scene at this point I fully expected them to call things off…
It turns out BOTB.com are a stern bunch though and they knuckled down, called for more towels and got on with getting the best out of the day that they could.
After lots of shots of the assembled cars (Audi R8, Mercedes SLS, Porsche 911, Aeriel Atom, Range Rover, Aston Martin Vantage and of course a Lotus Evora S front and centre…) David arrived and was taken off for some lunch and to be given his briefing on the shooting schedule.
If you’ve ever been to a photo shoot you’ll know it’s not as glamorous as it sounds and there is a LOT of waiting around, despite this everyone was in good spirits and I finally got to meet Rupert who had called me to tell me of my win. I also managed to say Hi to David and exchange notes on just how bloody cold it was at the Dan Wheldon Memorial Kart Race!
As the afternoon began to threaten to become evening I was finally called up to do an interview with DC. In my head I think I made a bit of a fluff of it as I spoke at a hundred miles an hour and started answering too many questions at once. Hopefully Ben managed to edit that into something usable…
After a quick video run through with Will and David one more shot was needed, a video of David driving the Merc SLS. Mention was made of a “donut” but given we had a big country house behind us and a big gravel driveway I was almost certain this would end badly… I hadn’t of course reckoned on David’s ability as a driver and he didn’t disappoint, TWICE!
Finally time was called due to lack of light and we all began to pick up our stuff and prepare to head M25wards to get home.
At this point DC stopped everyone and asked if anyone did “sundowners” round these parts and the resounding answer was of course “Yes”!
As 10 or so of us squeezed ourselves into the living room of the country house that had been the location for the shoot we all enjoyed a small glass of beer (Most of us were driving so it was small measures only of course!) and a really fun half hour followed where we discussed all kinds of things from the F1 coverage to the difficulties of constantly travelling through different timezones (That’s DC not me of course!) and into many other topics (mostly car related). For privacy reasons I won’t be listing any specifics as I think that would be unfair on a very public figure like David during such an informal situation.
!http://www.alunr.com/images/7.jpg (Me and David Coulthard)!
As darkness fell DC and his manager jumped into a waiting Mercedes (of course!) and headed back to London. I headed outside and coaxed the Evora off the wet lawn and headed back to the M25 to take the Evora back to it’s home.
As days go it was brilliant. DC really is a lovely bloke and quite grounded despite his F1 lifestyle, the ladies and gents from BOTB were brilliant as ever and as the icing on the cake, of course, was getting to play with an Evora S for the day. So thank you DC for trying to get a half decent interview out of me, Thank you Lotus/Lipscombs for the use of your Evora S for the day and a really big thanks to BOTB for inviting me and of course looking after me throughout the day.
So as well as being the lucky git who won the car I’m also the lucky git who because they won a car got to meet DC. Please don’t hate me…
Over the Xmas break I was lucky enough to be asked to write an article for the “12 Devs Of Xmas”:http://www.12devsofxmas.co.uk/ website. Despite my advancing years this was the first time I’d been asked to write about web development in a technical manner so it was a bit of a learning curve for me.
The article is a very simple introduction to CodeIgniter but will hopefully get people far enough into the framework to start playing for themselves. Enjoy!
“Get to grips with CodeIgniter”:http://www.12devsofxmas.co.uk/2011/12/codeigniter/
Hopefullly it has been useful to many of you, and if not then let me know how I can improve for the future as I intend to keep writing now as I thoroughly enjoyed the experience.