Values - SUBSTITUTE

Loading...

En provenance du cours de PwC

Problem Solving with Excel

2731 notes

PwC

Problem Solving with Excel

2731 notes

Cours 2 sur 5 dans Specialization Data Analysis and Presentation Skills: the PwC Approach

This course explores Excel as a tool for solving business problems. In this course you will learn the basic functions of excel through guided demonstration. Each week you will build on your excel skills and be provided an opportunity to practice what you’ve learned. Finally, you will have a chance to put your knowledge to work in a final project. Please note, the content in this course was developed using a Windows version of Excel 2013. This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.

À partir de la leçon

vLookups and Data Cleansing

In this module you will learn about VLookup, value cleansing and text functions. We will also introduce you to PwC's perspective on the value in cleansing data and using the appropriate functions. Finally, we will provide you an opportunity to perform a problem solving exercise using VLookup, value cleansing and text function.

Data Cleansing: Values - CLEAN- 1a2:27

Values - CLEAN -1b2:24

Values - TRIM1:36

Values - SUBSTITUTE2:41

Values - VALUE1:43

Values - TEXT2:40

Values - Stored as Text1:59

Rencontrer les enseignants

Alex Mannella
Alumni / Former Principal

[MUSIC]

In problem 3a, we're going to take the trim function one step further by

using the substitute function together with it.

Here in this data, we have a comma separating some of our numbers where we

would like decimal points or periods,

and at the same time,

we still have extra spaces that we talked about in the last exercise.

The substitute function allows us to change specific characters and

replace them with another.

I'll start by typing the trim function, =TRIM( and

I'll nest the substitute function into that.

So I'll type SUBSTITUTE and

that prompts us to choose the cell with text that we would like to replace,

so I'll reference the first cell in our table.

The next reference is the old text we would like to replace.

So I'll type open quote, comma, closed quote.

Notice that I put the comma within quotes because it's a text value.

All text values within a formula string need to be separated from

the formula with quotes.

So next, I will enter the substitute text in this example which is a period,

so I'll type quote, period, end quote. Next, I'll close the parentheses,

click Enter, and I'll drag down the formulas to our other cells.

Again, I'll click the Menu and fill without formatting

to ensure that our format stays the same as it was before in our table.

You might be wondering why we wouldn't just use the find and

replace function that's located in the home ribbon.

The reason is because it's always important to keep your raw data intact.

Especially if you have to do a lot of clean up.

If you have issues further down the line you may want to look back at the original

data to check.

Or you may need evidence of what you originally received.

If you start making changes and edits directly to your original data it is

extremely difficult to ensure that nothing has been changed accidentally.

Or there may be instances where you need to check if an issue was with the raw

data received or the computations done to it.

Therefore, it is a leading practice to use the reference functions that we've

learned together to clean and edit your data,

especially when you're dealing with sensitive or large data sets.

[MUSIC]

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2019 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play