MS Excel: SUBSTITUTE() Function : Full-Stack Web & Software Developer

Profile

Work

I am a Full-Stack Web & Software Developer with over fifteen years of experience. Over countless hours I have discovered that I have a passion and talent for creating works that are both highly functional and technically sound.

I have also discovered that I have a knack for understanding new theories & concepts – I am an adamant perfectionist when it comes to just about anything I do.

Current Skill set

Web

Responsive Mobile first HTML & CSS3 – Bootstrap, SASS
JavaScript (Vanilla JS, JQuery, Angular)
PHP (Web Server & CRON)
CRM / CMS packages – WordPress, OsCommerce, Marketo
MySQL
Git
SEO & PPC

Applications

Java
Python

Legacy Technology

ASP (classic)
Visual Basic
Adobe Flash

Software Packages

Adobe Photoshop
MySQL
Jet Brains Web Suite
Microsoft Word
Microsoft Excel
Microsoft Access
Maya 3d
and more…

Study

At the start of 2019, I am entering my 4th year of a 6 year course Studying a Bachelor of
Computer Science with gaming specialisation at Charles Sturt University

Hobbies

Pyrotechnic

A license Pyrotechnician in the state of N.S.W. Australia for over twenty years, performing
professional grade pyrotechnic shows ranging from from Chinese String Crackers, Indoor Close
Proximity Fireworks, Aerial Shells up to 125mm and Aerial Salutes up to 75mm.

A founding member of the Pyrotechnics Industry Association of Australia (PIAA), Based in Sydney
but have performing shows all over NSW for all types of events.

Car Enthusiast

An active member in the Skylines Australia NSW car club I regularly volunteer to help run events.

Social Media

You can connect with me professionally on LinkedIn,
or stalk me through Twitter

Portfolio

Employment

Wizardry Fireworks

PRODOCOM Australia

Hannover Fairs Australia

E-Web Marketing

Freelance Websites

Personal Projects

Tipping Comp

cruizen’

National Pyrotechnics

iblott accessories

Blog

MS Excel: SUBSTITUTE() Function

Microsoft’s Excel has a function called SUBSTITUTE(), this function can be used to completely find and replace a substring within a string.

Understanding the way Excel uses SUBSTITUTE()

This formula required 4 different inputs; HAYSTACK, NEEDLE, NEW NEEDLE, APPEARANCE.

The formula is as follows:

SUBSTITUTE(HAYSTACK,NEEDLE,NEW NEEDLE,APPEARANCE)

Using SUBSTITUTE()

If I had the string “Ian Blott, Web Developer, Sydney Australia” in cell A1 and I to moved to Seattle USA, I can use the SUBSTITUTE() formula to do this for me

SUBSTITUTE(A1,"Sydney Australia", "Seattle USA", 1)

This would return the value “Ian Blott, Web Developer, Seattle USA”.

The attribute APPEARANCE is a very powerful one that MS have included. With this I can deligate that the SUBSITUTE() will either work off the first instance of the NEEDLE, Second or even 30th by simply placing the desired number in the formula.

Side note:

As with all my posts, quite often they are going somewhere and require 2,3 or even 4 different functions to perform the final task. Bellow are the related posts that tell an overall story

Published by admin

April 14, 2011 8:38 am

Excel

Creative

I studied Digital Media for 12 months at Mt Druitt TAFE. While doing so we studied The 3D Modeling Program ‘Maya’. During the course I produced a number of 3D scenes and a couple of 3D animated movies.

I have also played in other programs such as Bryce 3D, 3D Studio Max, Lightwave, Vue D’esprit and a few others. However, i have always returned to Maya as a personal preference.

Full-Stack Web & Software Developer