TopicsCheat sheetLessons
Lurndo › Excel › Cleaning text

Learning Excel: Cleaning text

Cleaning up

Column F holds the names the way the guests typed them: with spaces at the front, all in capitals, or no capitals at all. Natalie can't print place cards like that.

TRIM

TRIM removes the spaces at the start and the end, and turns several spaces between words into one.

=TRIM(F2) turns Sadie's name into sadie de vere, without the two spaces at the front.

G2
fx

=TRIM(F2)

ABCDEFG
1First nameLast nameEmailTableMobileAs typedResult
2SadieDe Veresadie.devere@mail.co.ukT01-COD07700 900 467 sadie de veresadie de vere
3JamesPearsonjames.pearson@yahoo.co.ukT01-STEAK07700 900 233JAMES PEARSON
4AmiraEl Idrissiamira@elidrissi.co.ukT02-VEG07700 900 654amira el idrissi
5LiamWaltersliam.w@gmail.comT02-COD07700 900 566Liam Walters
6FayeClarkfaye.clark@hotmail.co.ukT03-STEAK07700 900 456faye clark
7BillMossbill@moss-bakery.co.ukT03-VEG07700 900 321 BILL moss
8LibbyJenningslibby.jennings@yahoo.co.ukT04-COD07700 900 899libby jennings
9OwenVan Dykeowen.vandyke@mail.co.ukT04-STEAK07700 900 030owen VAN DYKE
10

PROPER

PROPER gives every word a capital first letter and makes the rest lower case. UPPER makes everything capitals, LOWER everything lower case.

You can put functions inside each other: =PROPER(TRIM(F2)) first removes the spaces and then fixes the capitals: Sadie De Vere.

G2
fx

=PROPER(TRIM(F2))

ABCDEFG
1First nameLast nameEmailTableMobileAs typedResult
2SadieDe Veresadie.devere@mail.co.ukT01-COD07700 900 467 sadie de vereSadie De Vere
3JamesPearsonjames.pearson@yahoo.co.ukT01-STEAK07700 900 233JAMES PEARSON
4AmiraEl Idrissiamira@elidrissi.co.ukT02-VEG07700 900 654amira el idrissi
5LiamWaltersliam.w@gmail.comT02-COD07700 900 566Liam Walters
6FayeClarkfaye.clark@hotmail.co.ukT03-STEAK07700 900 456faye clark
7BillMossbill@moss-bakery.co.ukT03-VEG07700 900 321 BILL moss
8LibbyJenningslibby.jennings@yahoo.co.ukT04-COD07700 900 899libby jennings
9OwenVan Dykeowen.vandyke@mail.co.ukT04-STEAK07700 900 030owen VAN DYKE
10

SUBSTITUTE

SUBSTITUTE replaces one piece of text with another: you give the text, what has to go and what comes in its place.

=SUBSTITUTE(E2," ","") replaces every space with nothing: 07700900467, ready for the text message service.

G2
fx

=SUBSTITUTE(E2," ","")

ABCDEFG
1First nameLast nameEmailTableMobileAs typedResult
2SadieDe Veresadie.devere@mail.co.ukT01-COD07700 900 467 sadie de vere07700900467
3JamesPearsonjames.pearson@yahoo.co.ukT01-STEAK07700 900 233JAMES PEARSON
4AmiraEl Idrissiamira@elidrissi.co.ukT02-VEG07700 900 654amira el idrissi
5LiamWaltersliam.w@gmail.comT02-COD07700 900 566Liam Walters
6FayeClarkfaye.clark@hotmail.co.ukT03-STEAK07700 900 456faye clark
7BillMossbill@moss-bakery.co.ukT03-VEG07700 900 321 BILL moss
8LibbyJenningslibby.jennings@yahoo.co.ukT04-COD07700 900 899libby jennings
9OwenVan Dykeowen.vandyke@mail.co.ukT04-STEAK07700 900 030owen VAN DYKE
10

Try it yourself

In G2, make a tidy version of the name as typed and fill down to G9.

ABCDEFGH
1First nameLast nameEmailTableMobileAs typedResult
2SadieDe Veresadie.devere@mail.co.ukT01-COD07700 900 467 sadie de vere
3JamesPearsonjames.pearson@yahoo.co.ukT01-STEAK07700 900 233JAMES PEARSON
4AmiraEl Idrissiamira@elidrissi.co.ukT02-VEG07700 900 654amira el idrissi
5LiamWaltersliam.w@gmail.comT02-COD07700 900 566Liam Walters
6FayeClarkfaye.clark@hotmail.co.ukT03-STEAK07700 900 456faye clark
7BillMossbill@moss-bakery.co.ukT03-VEG07700 900 321 BILL moss
8LibbyJenningslibby.jennings@yahoo.co.ukT04-COD07700 900 899libby jennings
9OwenVan Dykeowen.vandyke@mail.co.ukT04-STEAK07700 900 030owen VAN DYKE
10
G2

Ready for more?

The full lesson has 4 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson
← PreviousSticking text togetherNext →Dates are numbers

SQL

  • Topics
  • All lessons
  • Cheat sheet

Excel

  • Topics
  • All lessons
  • Shortcuts

HTML & CSS

  • About the course
  • All lessons

Lurndo

  • Dashboard
  • Leaderboard
  • Premium
  • Account
  • Privacy
Nederlands