How to Remove Spaces from an Excel String Cell

Document

 Easy Formulas to Remove Spaces

How to use Excel formulas to remove leading, trailing, and extra spaces
Excel formulas to remove spaces and clean your data efficiently.

Simply, the paces inside an Excel string Cell are either:

  • leading/starting spaces
  • Mid spaces
  • Trailing/ending spaces
Plus, if you are exporting the data from the internet or a database, you may have some problematic characters (unprintable or 7-bit ASCI characters). We will handle this too.

✋ Before we start, we will assume that the data is in cell A1. We will insert the formulas starting from B1.Feel free to change cells reference to your references.

How to remove all extra spaces from an Excel cell?

This will include leading, mid, and trailing spaces

=TRIM(A1)

or

=SUBSTITUTE(A1," ","")

Remove Spaces and Clean Unprintable Characters

=TRIM(CLEAN(A1))

You may still see characters like(� , ⌷ , ¶) which are called "unprintable characters". How to remove them? 

 Most famous codes for the unprintable characters are:

  • char(160) → �
  • char(127) → ⌷
  • char(182) → ¶

To remove these characters, you add them inside a SUBSTITUTE function (or a nested SUBSTITUTE function).
We will assume that we both � and ⌷ inside our string cell. So, we must have 2 SUBSTITUTE functions nested for each. Remember: � is char(160), ⌷ is char(127).

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),""),CHAR(127),"")))


And a bonus:

How to delete blank rows with keyboard shortcuts:

How to quickly delete blank rows in Excel using Go To Special
Step-by-step guide to delete blank rows and clean your Excel dataset.

  1. Click on top of the column to highlight it all. or, inside any cell of the column Ctrl+Spacebar
  2. Press F5
  3. Click on Special
  4. Click on Blanks
  5. Press on Ctrl+-
  6. Choose "Entire row" and press ok or Enter


Comments

Post a Comment

Your opinion matters, your voice makes us proud and happy. Your words are our motivation.

Popular posts from this blog

فرصتك للدراسة في ألمانيا: منحة ممولة بالكامل لطلاب الدراسات العليا

Image
منح الدراسات العليا في ألمانيا (Scholarships for Graduate Studies in Germany) DAAD ألمانيا - منحة دراسة من فرصة ذهبية للدراسة في ألمانيا: منحة دراسية ممولة بالكامل لطلاب الدراسات العليا نظرة عامة: يسّر برنامج المنح الدراسية الألمانية دااد DAAD أن يقدّم لكم الفرصة لمواصلة تعليمكم الأكاديمي في ألمانيا من خلال دراسة عليا. يتم تمويل هذه المنح الدراسية من قبل وزارة الخارجية الألمانية الاتحادية. من يمكنه التقدم؟ يمكن التقدّم لمن أكملوا الدرجة الجامعية الأولى (بكالوريوس أو دبلوم على سبيل المثال) كحد أقصى بحلول وقت بدء فترة التمويل. يرجى ملاحظة: بالنسبة للمتقدمين من التخصصات الفنية ومجال الهندسة المعمارية ، تقدم DAAD برامج المنح الدراسية التالية: العمارة Architecture فن الأداء Performing Art الموسيقى Music الفنون الجميلة / التصميم / السينما Fine Art/Design/Film ما الذي يمكن تمويله؟ يوفر البرنامج التمويل ل: برنامج كامل للدراسات العليا أو درجة الماجستير مكتمل في إحدى الجامعات الحكومية أو المعترف بها من قبل الدولة في ألمانيا أو سنة واحدة من الدراسة في ألمانيا كجزء من درجة ثانية أو ماجستير مكتملة ...
Read more »»

How to Deactivate Screen Reader in Kali Linux

Image
 Deactivating Screen Reader in Kali Linux How to deactivate screen reader in Kali Linux Sometimes, you suddenly hear a screen reader whenver you hover on a menu with your mouse inside Kali Linux machine. This behavior even starts from the login screen, and continues to reading anything you write in the terminal.  Before moving on to the solution, let's get to know know the problem first.  What is ORCA or the Screen Reader in Kali Linux? The screen reader in this case is called "ORCA". Orca in the context of Kali Linux, or any other Linux distribution, is a free, open-source screen reader that provides access to the graphical desktop environments. It is designed to help visually impaired users use software applications and navigate the operating system. Orca works by converting on-screen content into speech and Braille output, enabling users who are blind or have low vision to interact with their computers more easily. If this behavior is annoying or it has been acced...
Read more »»

Murphy's Law: Expect the Unexpected

Image
  Have you ever heard of Murphy's Law? What is Murphy's Law? It's a principle that states, "Anything that can go wrong will go wrong." This law, also known as Murphy's Principle or Murphy's Rule, highlights the tendency of things to go awry and cause problems. Understanding Murphy's Law can help us prepare for potential setbacks and approach them with a proactive mindset. In this blog post, we'll explore the essence of Murphy's Law and its significance in various fields. The Origin of Murphy's Law: Murphy's Law finds its roots in the work of an American aerospace engineer named Edward Murphy. During the 1950s, Murphy discovered that errors in technical designs often occurred due to the neglect or oversight of potential failure factors. This realization led to the formulation of what we now know as Murphy's Law. At its core, Murphy's Law suggests that if something has the potential to go wrong, it will undoubtedly go wrong at som...
Read more »»

Python 3.2.1.14 LAB: Essentials of the while loop

Image
3.2.1.14 LAB: Essentials of the while loop Essentials of the While Loop If you're taking PCAP - Programming Essentials In Python , you may have encountered this question 3.2.1.14 LAB: Essentials of the while loop. Objectives Familiarize the student with: using the while loop; finding the proper implementation of verbally defined rules; reflecting real-life situations in computer code. Scenario Listen to this story: a boy and his father, a computer programmer, are playing with wooden blocks. They are building a pyramid. Their pyramid is a bit weird, as it is actually a pyramid-shaped wall - it's flat. The pyramid is stacked according to one simple principle:  👉 each lower layer contains one block more than the layer above 👈 . The figure illustrates the rule used by the builders: building blocks Your task is to write a program which reads the number of blocks the builders have, and outputs the height of the pyramid that can be built using these blocks. Note: the height is m...
Read more »»

7 Reasons Why You Need to Learn English

Image
Why you need to Learn English Language Right Now? English Learning Open the Door to the World: Why English is Essential in Today's Landscape English has transcended its origins to become the lingua franca of our interconnected world. Its significance extends far beyond casual conversation, offering a key to unlock countless opportunities across various domains. Whether you envision professional growth, academic exploration, enriching travel experiences, or simply expanding your horizons, mastering English empowers you to connect, contribute, and thrive in a globalized environment. Invest in Your Future: In today's competitive job market, fluency in English sets you apart. From multinational corporations to international organizations, English proficiency is often a prerequisite for securing dream jobs and pursuing lucrative careers. This language opens doors to top universities and research institutions, granting access to a wealth of knowledge and esteemed academic programs. B...
Read more »»

Data Analysis Roadmap 2026: From Excel Lover to Python-Powered Analyst

Image
 Data Analysis Roadmap 2026 Data Analysis Roadmap 2026 Ever looked at a spreadsheet and thought, "There has to be a smarter way to do this"? You're right. The difference between an Excel user and a data analyst isn't magic—it's a clear roadmap. In 2026, data analysis skills are non-negotiable. Companies aren't just collecting data anymore; they're drowning in it. And those who can turn raw numbers into actionable insights? They're the ones shaping decisions, driving revenue, and getting hired first. Here's your practical 6-month blueprint to transform from "I use Excel" to "I build automated analytics pipelines." The Four Pillars Every Data Analyst Needs: Think of data analysis like building a house. You can't jump straight to the roof. You need a solid foundation, proper structure, electrical systems, and interior finishing. Same with analytics. Here are the four pillars that successful analysts master: 1. **Data Literac...
Read more »»

15 Python Builtin-Functions You Need to Master as a Beginner

Image
  What are the important 15 Python Builtin-Functions You Should Know as a Beginner? 15 Python Builtin-Functions You Should Know What should you do as a Python Beginner? As a beginner in Python programming, it's important to familiarize yourself with the built-in functions that are essential for writing efficient and clean code. In this article, we will explore 15 Python built-in functions that you need to master in order to become proficient in the language. You will notice in the examples below - most of them at least - that we did not give you the output. Use the keyboard and apply them on a file, and let's call it main.py. You are a programmer now, not a normal person to read and watch only. And now, let's begin... 1. `print()`: This function is used to display the output of a program. It is a fundamental function that allows you to showcase results and information to the user. Example: print("Hello, World!") name = "Alice" print("Hello,", n...
Read more »»

Prefixes vs Suffixes ? What are they?

Image
Prefixes: As per the dictionary, "pre" means previous to or before. So, prefixes are a group of letters placed before the original/root word. Examples and meanings: Un happy: un (prefix) means not . happy (root word, adjective). un happy means not happy. Re appear: re (prefix) means  again or back . appear (root word, verb). re appear means to appear again. Suffixes: Suffixes are a group of letters placed After/onto the end of  the original/root word. Examples and meanings: de value: value (root word, noun). de- (prefix) changes the noun to a verb with the opposite meaning. de- only means reverse, away, opposite Strong er : Strong (root word, adjective). -er ( suffix ) changes the adjective to a comparative meaning between 2 people or things. "He is strong, but his brother is stronger than him." Act ing : Act  (root word, verb). - ing ( suffix ) changes the verb to a noun/action. "She was not good at acting, so she changed her career to Engineering." ...
Read more »»

Python 4.3.1.7 LAB: How many days: writing and using your own functions

Image
writing and using your own functions writing and using your own functions Prerequisites LAB 4.3.1.6 Objectives Familiarize the student with: projecting and writing parameterized functions; utilizing the return statement; utilizing the student's own functions. Scenario Your task is to write and test a function which takes two arguments (a year and a month ) and returns the number of days for the given month/year pair (while only February is sensitive to the year value , your function should be universal). The initial part of the function is ready. Now, convince the function to return None if its arguments don't make sense.  What is needed Of course, you can (and should) use the previously written and tested function ( LAB 4.3.1.6 ). It may be very helpful. We encourage you to use a list filled with the months' lengths . You can create it inside the function - this trick will significantly shorten the code. We've prepared a testing code. Expand it to include more test ...
Read more »»

Python 4.3.1.6 LAB: A leap year: writing your own functions

Image
If you're taking PCAP - Programming Essentials In Python , you may have encountered this question  4.3.1.6 LAB: A leap year: writing your own functions Objectives Familiarize the student with: projecting and writing parameterized functions; utilizing the return statement; testing the functions. Scenario Your task is to write and test a function which takes one argument (a year) and returns True if the year is a leap year, or False otherwise. The seed of the function is already sown in the skeleton code in the editor. Note: we've also prepared a short testing code, which you can use to test your function. The code uses two lists - one with the test data, and the other containing the expected results. The code will tell you if any of your results are invalid. Important to know first: Normal year is 365 days (actually they're 356.25 days as per Nasa) Leap year is 366 days . Which means, leap year has an additional day added in February which makes it 29 days. A leap year occ...
Read more »»