Formulas or Power Query or Regex: Why Do I Get Zero When I SUM? Excel Magic Trick 1925
From excelisfun · · 13 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.
If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.
Description
Download Excel File: https://excelisfun.net/files/EMT1925.xlsx Mike excelisfun Girvin Excel books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=sr_ntt_srch_lnk_1&ccs_id=5f0ec91c-7a28-4a2b-9c87-04a7ee3159e6 Learn how to convert text numbers to real numbers so you can SUM to get a total. Learn about character 32, spaces, and non-breaking spaces, character 160, and how to remove them from numbers to convert them to real numbers. This is an important trick because we often get numbers from the internet or download files that have the dreaded character 160. Learn about the functions: LEN, SEQUENCE, MID, SUBSTITUTE, CHAR, CODE, REGEXREPLACE, and learn about Power Query. Topics: 1. (00:00) Introduction 2. (00:16) Painting with Mike excelisfun Girvin 3. (00:26) Add numbers, but we get zero!?!?! 4. (00:46) File with data and the many problems with storing data 5. (00:58) Why Center Alignment is so terrible 6. (01:25) How to Remove Formatting to view real data 7. (01:50) Learn important keyboard shortcuts, and history behind it! 8. (02:03) How to get Alt Keyboards to work 9. (02:39) Do Not Format Cells in Workbook that are not used. DO NOT USE COLUMN HIGHLIGHT ARROW! 10. (03:28) Alternative to Column Highlight Arrow 11. (04:01) Convert text numbers with any math operation, such as Double Negative 12. (04:26) Double Negative and Unitary Operator which evaluates quickly 13. (05:32) What is a #VALUE! Error? 14. (05:50) Use Formulas to investigate what characters are in a cell with digits. 15. (06:28) LEN Function 16. (06:36) General Number Format to remove all Number Formatting 17. (06:45) SEQUENCE Array Function 18. (06:56) MID Function 19. (07:25) CODE function to reveal the code number for a character 20. (08:20) SUBSITUTE function to remove character 160 21. (08:30) CHAR function 22. (09:14) REGEXREPLACE function and regex code to remove white space such as space, tab and non-breaking space 23. (11:22) Bonus #1: Power Query to that remove spaces and non-breaking spaces 24. (11:53) Bonus #2: Excel Table Column Formula to add student marks that removes spaces and non-breaking spaces 25. (12:34) Dynamic Spilled Arrays in Excel Table Columns: Error or No Error? 26. (13:11) Bonus #3: CHAR(160) in pattern argument of REGEXREPLACE function 27. (13:30) Summary 28. (13:36) Closing
This video has subtitles or audio translations in these 117 languages: Abkhazian, Afar, Afrikaans, Akan, Albanian, Arabic, Armenian, Azerbaijani, Bambara, Bangla, Bashkir, Basque, Belarusian, Bosnian, Bulgarian, Burmese, Cantonese, Chinese (Hong Kong), Chinese (Singapore), Chinese (Taiwan), Croatian, Czech, Danish, Dutch, Dutch (Netherlands), Estonian, Faroese, Fijian, Filipino, Finnish, French, French (France), Georgian, German, German (Germany), Greek, Hawaiian, Hebrew, Hindi, Hungarian, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean, Kurdish, Lao, Latin, Latvian, Lithuanian, Luxembourgish, Macedonian, Maithili, Malagasy, Malay, Malayalam, Mongolian, Nepali, Norwegian, Papiamento, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Punjabi, Romanian, Russian, Samoan, Sango, Sanskrit, Santali, Scottish Gaelic, Serbian, Serbian (Cyrillic), Serbian (Latin), Sicilian, Sindhi, Sinhala, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Sundanese, Swahili, Swati, Swedish, Tajik, Tamil, Tatar, Telugu, Thai, Tibetan, Tigrinya, Tsonga, Tswana, Turkish, Turkmen, Ukrainian, Urdu, Uyghur, Uzbek, Venda, Vietnamese, Welsh, Western Frisian, Yiddish, Yoruba, Zulu. Click gear icon below video to set subtitles, audio and language.
#excel #microsoft #excelisfun #excelcourse #arrayformulas #dynamicspilledarray #sum #sumnotwork #total #sumfunction #sequence #len #code #char #Mid #midfunction #regex #REGEXREPLACE #substitute #textnumbers #char160 #nonbreakingspace