Manipulating text data

Data Analysis in Google Sheets

James Chapman

Curriculum Manager, DataCamp

How long is a string?

  • Example: COST: $1

A string split into a chain of different characters.

Data Analysis in Google Sheets

How long is a string?

  • Example: COST: $1

A string split into a chain of different characters.

Data Analysis in Google Sheets

How long is a string?

  • Example: COST: $1

A string split into a chain of different characters.

Data Analysis in Google Sheets

How long is a string?

  • Example: COST: $1

A string split into a chain of different characters.

Data Analysis in Google Sheets

How long is a string?

  • Example: COST: $1

A string split into a chain of different characters.

= LEN("COST: $1")

8
Data Analysis in Google Sheets

Searching for characters

Syntax:

SEARCH(search_for, text_to_search, [starting_at])
  • search_for: String to search for
  • text_to_search: Text to search through
  • starting_at (default = 1): Index to start at
Data Analysis in Google Sheets

Searching for characters

Syntax:

SEARCH(search_for, text_to_search, [starting_at])

 

Example: Find $ in COST: $1

= SEARCH("$", "COST: $1")

7
Data Analysis in Google Sheets

Extracting text - LEFT() and RIGHT()

Syntax:

LEFT(string, [number_of_characters])

 

Example:

= LEFT("COST: $1", 4)

COST

Syntax:

RIGHT(string, [number_of_characters])

 

Example:

= RIGHT("COST: $1", 2)

$1
Data Analysis in Google Sheets

Bringing it together

Example: Extract the state from the location data

 

Extracting the state from location data.

Data Analysis in Google Sheets

Bringing it together

Example: Extract the state from the location data

 

Extracting the state from location data.

Data Analysis in Google Sheets

Bringing it together

Syntax:

RIGHT(string, [number_of_characters])

Extracting the state from location data.

Data Analysis in Google Sheets

Bringing it together

Extracting the state from location data.

Data Analysis in Google Sheets

Bringing it together

Syntax:

SEARCH(search_for, text_to_search, [starting_at])

Extracting the state from location data.

Data Analysis in Google Sheets

Bringing it together

Syntax:

SEARCH(search_for, text_to_search, [starting_at])

Extracting the state from location data.

Data Analysis in Google Sheets

Bringing it together

Syntax:

SEARCH(search_for, text_to_search, [starting_at])

Extracting the state from location data.

Data Analysis in Google Sheets

Substituting characters

Syntax:

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
  • text_to_search: the text to search through
  • search_for: the string to search for
  • replace_with: the replacement string
  • occurrence_number: which occurrence should be substituted
Data Analysis in Google Sheets

Substituting characters

Syntax:

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

 

Example:

= SUBSTITUTE("I like DataCamp!", "like", "love")

I love DataCamp!
Data Analysis in Google Sheets

Let's practice!

Data Analysis in Google Sheets

Preparing Video For Download...