To turn a value such as AB 123 into AB123, enter =SUBSTITUTE(A1," ","") in a helper cell and fill it down. That formula removes every ordinary space in the source text. If spaces between words must remain, use =TRIM(A1) instead. For data copied from web pages or external systems, use =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) to handle nonbreaking spaces and nonprinting characters. Microsoft 365 and Excel for the web also support a regular-expression formula that removes spaces only where a letter touches a digit.
The right method depends on whether you want to remove all spaces, normalize word spacing, target only letter-number boundaries, clean a one-time range, or build a repeatable import process. The procedures below show what each option changes, how to preserve the original data, and how to convert cleaned numeric text into real numbers.
As an Amazon Associate I earn from qualifying purchases.
Choose the method that matches the data
“Remove spaces” can mean several different operations. A product code, postal code, ticket ID, or account number may require every space to disappear. A sentence usually needs its single spaces preserved while extra leading, trailing, or repeated spaces are corrected. Imported text can contain character 160 (a nonbreaking space) or control characters that look like ordinary spaces but defeat a simple formula.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →| Requirement | Best first choice | What it changes | Important limitation |
|---|---|---|---|
| Delete every ordinary space | =SUBSTITUTE(A1," ","") |
Removes all ASCII space characters in the cell | Also removes spaces that may be meaningful in prose |
| Keep one space between words | =TRIM(A1) |
Removes leading and trailing spaces and collapses runs to one space | Does not remove nonbreaking space 160 by itself |
| Clean copied or imported text | =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) |
Converts nonbreaking spaces, removes control characters, then normalizes ASCII spacing | Still preserves one space between ordinary words |
| Remove only spaces directly between letters and digits | REGEXREPLACE formula |
Targets letter-space-digit and digit-space-letter boundaries | Requires a supported Microsoft 365 or Excel for the web version |
| Clean a range once | Find and Replace | Edits the selected cells in place | Easy to remove spaces from the wrong fields if the selection is too broad |
| Apply the same cleanup whenever data refreshes | Power Query | Stores a transformation step in the query | Requires the data to be loaded or refreshed through Power Query |
Before editing, copy the source column or work in a new column. That gives you an audit trail and lets you compare the cleaned value with the original when an unexpected result appears.
#1 Best Overall
- BLUE LIGHT BLOCKING GLASSES: Our blue light glasses for women boast UV400 protection, blocking harmful blue rays from computer screens. With these bluelight glasses women, you can enjoy gaming, reading, or watching TV with no worries of eye strain.
- EYE RELIEF: Our blue light glasses are engineered to reduce blurry vision or strain caused by prolonged screen time. These womens blue light glasses aid in better sleep by preventing blue light from disrupting your natural sleep cycle, helping you fall asleep faster and enjoy a more restful night's sleep, making them the perfect christmas present.
- COMFORTABLE: Crafted from durable material, our trendy blue light glasses for woman or blue light glasses men ensure long-lasting comfort. The high-transmittance lenses accessories, gamer gifts for men, help keep your eyes relaxed. Perfect stocking stuffers, gifts for gamers men, christmas gifts for men, halloween costumes.
- LIGHTWEIGHT: These clear blue light glasses feature a lightweight frame, meaning they’ll stay comfy for long-term wear. Perfect as blue blockers glasses for women or mens blue light glasses, these non-prescription fake glasses for men can be worn throughout the day. The blue light blocking glasses women feature polycarbonate HD lenses, which are anti-reflective and restore true color.
- WORRY-FREE PURCHASE: If you experience any issues, our support team is ready to assist you quickly. These are the perfect costume glasses for cosplay dressing up.
Remove every ordinary space with SUBSTITUTE
Formula
In a blank column beside the first source value, enter:
=SUBSTITUTE(A1," ","")
Here, A1 is the cell containing the original text, the second argument is one ordinary space, and the third argument is an empty string. If A1 contains AB 123, the result is AB123. If it contains North Valley 7, the result is NorthValley7 because every ordinary space is removed.
Fill the formula through the column
- Insert a blank column next to the source data and label it, for example, Cleaned code.
- Enter
=SUBSTITUTE(A1," ","")in the first row of that column. - Press Enter and check the result against the source cell.
- Drag the fill handle down, double-click it, or copy and paste the formula through the used rows.
- If a system requires fixed text rather than formulas, select the results, copy them, and use Paste Special > Values in a destination column.
When this is safe
Use this approach for fields whose definition says that spaces are never significant: internal IDs, serial numbers, inventory codes, and compact labels. Do not use it on addresses, names, sentences, or any field where word separation carries meaning. A formula cannot know whether a space is accidental or part of the value; the field’s rules must make that decision.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPreserve word spacing with TRIM
What TRIM removes
Microsoft documents TRIM as removing all spaces except single spaces between words. It removes spaces at the beginning and end of a text value and changes repeated runs inside the value to one space. For example, a value with three spaces before AB, four spaces between AB and 123, and two trailing spaces becomes AB 123. The space between the letter group and number remains because TRIM is designed to normalize prose, not concatenate code parts.
Use:
=TRIM(A1)
Typical uses
- Cleaning names, descriptions, comments, and addresses while preserving readable word separation.
- Removing accidental spaces that stop an exact text match from working.
- Normalizing data before comparing two columns or removing duplicates.
TRIM was designed for the 7-bit ASCII space character, code 32. A visually identical nonbreaking space, code 160, can remain after =TRIM(A1). If a value appears clean but still fails a lookup, comparison, or validation rule, test for that hidden character and use the combined cleanup formula described next.
Clean hidden spaces and control characters from imported data
The combined formula
For text pasted from a web page, PDF, email, or external export, use:
Rank #2
- WINDEX GAMING WIPES: effortlessly clean all your gaming gear and gaming electronics in one easy step with our new Windex Gaming Wipes
- AMMONIA FREE: our ammonia-free formula is safe to use on all of your favorite gaming equipment like consoles, headsets, controllers, screens, keyboards, and more
- PRE-MOISTENED WIPES: each wipe instantly removes dust, dirt, and stubborn smudges and fingerprints leaving a streak-free shine
- ANTI-STATIC: great for cleaning and wiping down your gaming gear and electronics leaving behind no visible residue
- COMPACT RESEALABLE PACKAGE: store your pack of Windex Gaming Wipes anywhere in your home for whenever a quick and easy wipedown is needed
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
The functions run from the inside out:
CHAR(160)represents the nonbreaking space character commonly found in copied or imported text.SUBSTITUTE(A1,CHAR(160)," ")changes each nonbreaking space into a normal ASCII space.CLEAN(...)removes low-level nonprinting characters. Microsoft describes CLEAN as removing the first 32 nonprinting characters in the 7-bit ASCII range, values 0 through 31.TRIM(...)removes leading and trailing ordinary spaces and reduces repeated ordinary spaces to one.
This formula intentionally preserves one space between words. If the cleaned field is a code in which no spaces are allowed anywhere, wrap the cleaning stages differently or remove the final spaces with another SUBSTITUTE:
=SUBSTITUTE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))," ","")
Use the second version only when every space, including legitimate word separators, must disappear.
Diagnose a space that will not go away
If the visible value still behaves differently from an apparently identical value, inspect the character codes around the suspected position. A simple test for a nonbreaking space is:
=ISNUMBER(SEARCH(CHAR(160),A1))
A TRUE result means the cell contains character 160 somewhere. To inspect a particular position, use =CODE(MID(A1,position,1)), replacing position with the character number you want to examine. Code 32 is an ordinary ASCII space; code 160 is a nonbreaking space. Other values in the 0–31 range may indicate control characters that CLEAN can remove.
Free tools Windows power users keep installed
One-click scans. No signup required.
Remove only spaces between letters and numbers with REGEXREPLACE
Formula for Microsoft 365 and Excel for the web
When spaces elsewhere must remain, use:
=REGEXREPLACE(A1,"(?<=[A-Za-z]) +(?=[0-9])|(?<=[0-9]) +(?=[A-Za-z])","")
The expression has two alternatives separated by |. The first finds one or more spaces preceded by a letter and followed by a digit. The second finds one or more spaces preceded by a digit and followed by a letter. Lookbehind and lookahead test the neighboring characters without including them in the replacement. Thus, AB 123 becomes AB123, 123 AB becomes 123AB, and North Valley remains North Valley.
Rank #3
- Ergonomic & Adjustable Design: Fight "text neck" and improve your posture. Designed for comfort, it offers an adjustable height (7.1in-8.5in) (1.4in adjustable range) and angle (5°-85°) Please note: cell phone stand is only 9 inches tall—the length of a standard pencil
- Without blocking subtitles Perfect for office desk Tiktok Face Time Zoom video—this is the ultimate kitchen desk essential for phones! Perfect viewing experience while reducing neck back strain, A must-have for gamers streamers desk, and anyone who values both style and support!
- Sleek & Stable Design fits Otterbox Case: Say goodbye to bent cables, tangles, and tip-overs! LISEN’s stylish adjustable desk phone holder features a built-in charging hole and smart cable management, keeping your phone secure and your workspace clutter-free cool gadgets for men
- ANTI-SLIP DESIGN free hands: The under 10.00 items easy gadgets back and the bottom of this kitchen essentials for women are fully covered by anti-skid silicone, which LISEN stand up desk decor can provide maximum protection device from any scratches and slides
- Your Perfect Viewing Companion:If your product arrives damaged or breaks within 365 days, reach out to LISEN for a quick and hassle-free solution. Struggling with assembly, like tightening the screw? We’ve got your back! Your experience matters to us. (Product including: phone Holder stand*1, screw*1, hexagon screwdriver*1, specification*1)
Examples of what the pattern changes
| Input | Result | Reason |
|---|---|---|
AB 123 |
AB123 |
Letter followed by spaces and digits |
123 AB |
123AB |
Digits followed by spaces and a letter |
AB 123 |
AB123 |
The plus sign matches the entire run of spaces |
North Valley |
North Valley |
Neither side of the space is a digit |
12 34 |
12 34 |
The pattern does not target digit-to-digit spacing |
A B |
A B |
The pattern does not target letter-to-letter spacing |
Check compatibility before sharing the workbook
REGEXREPLACE is documented for Microsoft 365 and Excel for the web. If the recipient opens the workbook in an older perpetual edition or another spreadsheet application, the formula may return an unknown-function error. Confirm the target environment before distributing a file that depends on it. For a mixed-version team, use a helper-column approach based on known patterns or perform the cleanup in Power Query instead.
Limits of the letter-and-digit pattern
The character classes [A-Za-z] cover unaccented English letters. They do not include every letter in every writing system, and they do not classify punctuation as a letter. If your codes contain accented or non-Latin characters, test representative values and adapt the pattern to the character set your data actually uses. The expression also leaves spaces between digits, between letters, and around punctuation untouched by design.
One-time cleanup with Find and Replace
Remove all ordinary spaces from a selected range
- Select only the cells or column that is allowed to lose every space.
- Open Home > Find & Select > Replace.
- In Find what, enter one ordinary space. You can press the spacebar once.
- Leave Replace with empty.
- Choose Replace All.
- Review the reported replacement count and inspect several cells, including values with multiple words and blank cells.
Find and Replace is appropriate for a one-time worksheet correction or a controlled cleanup before export. It changes the selected cells directly, so save a copy first if the original spacing may be needed later. Selecting an entire sheet can remove spaces from headings, notes, addresses, and formulas’ displayed text when the intent was limited to one data field.
Why Find and Replace may miss a visible space
The dialog’s single-space search looks for character 32. It will not necessarily match a nonbreaking space (160) or another hidden character. For those cases, use the helper formula with CHAR(160), or use Power Query’s special-character replacement. A pasted nonbreaking space can look identical on screen while remaining in the cell’s underlying text.
Build a repeatable cleanup in Power Query
Power Query is preferable when the same external file or table is imported repeatedly. A formula column can be copied, but a query step runs again when the source is refreshed and keeps the transformation separate from the raw data.
Replace an unwanted character
- Open the query in Power Query Editor.
- Select the text column that contains the codes or imported values.
- Choose Home or Transform > Replace Value, depending on the editor layout.
- Enter the ordinary space or the unwanted character in the value-to-find box.
- Leave the replacement value empty when the character should be deleted, then confirm.
- For nonbreaking spaces, insert the special character in the find box as supported by the editor, or first replace it with an ordinary space and apply a trim operation.
- Review the preview, then choose Close & Load (or the equivalent load command) to return the transformed table to Excel.
Power Query records the replacement as an applied step. On the next refresh, the same operation runs against the newly imported rows. Keep the raw query output available when possible; it makes it easier to prove whether a later discrepancy came from the source file or the transformation.
Rank #4
- Easy Installation: Our gaming lights have been designed to enhance your entertainment experience. Simply position the led light bar next to your TV or game console to create a game room environment with ease. They come with USB power and a 60 inch cord for direct mounting and mobility
- Audio Syncing & Extensive Mode: The built-in high-sensitivity music sensor allows you to sync the TV led lights to your favorite music. Lights react and move to the beat of music, film and game audio, creating an incredible immersive sound and light experience – and all without the need for an additional hub. You can choose from 64+ patterns, including strobe, illusion, jump, energy, gradient, running, chasing, fluttering mode etc. Each mode can be applied to different scenes
- 64+ Chasing IC+RGB & W Effect: The independent control design of IC+RGB and white all-in-one multifunctional rgbw led bar lights monitor backlight allows you to easily switch the led lights for desk between IC+RGB and white to enjoy a variety of lighting effects, you can choose white lighting mode with high brightness for better illumination when working, etc. Choose IC+RGB lighting mode when you need to create an entertaining atmosphere for the light show, 16 million colours of Color changing rgb light bar let you dance to the beat and create a dynamic party atmosphere
- Dynamic and Dimmable Lights: 1%-full brightness free brightness adjustment gives you the flexibility to adjust dimmable led light bar for room decorations or a variety of occasions
- USB Powered: USB light bars for room can be powered by powernank, usb charger, TV, Monitor, laptop PC mainframe, making behind TV led lighting bar easy to install in any location.TV led lights are powered by the TV usb interface / PC mainframe, can be turned on/off synchronously with TV / PC
Choosing Power Query over formulas
- Choose it for recurring imports, multiple cleanup steps, or a process maintained by several people.
- Choose a worksheet formula when users need to see and adjust the transformation row by row.
- Choose Find and Replace when the correction is a deliberate, one-time edit and the affected range is unambiguous.
Convert cleaned numeric text into a real number
Removing a space does not automatically change text into a numeric value. A cell that displays 1234 may still be text, which can break arithmetic, sorting, validation, and charting.
Use VALUE in a formula
For a numeric text value containing ordinary spaces, use:
=VALUE(SUBSTITUTE(A1," ",""))
For imported values that may contain nonbreaking spaces and controls, combine the cleanup first:
=VALUE(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," ")," ","")))
The second formula removes all spaces after converting character 160, cleans controls, and then passes the result to VALUE. Use it only for fields where spaces are never meaningful and where the remaining text is a valid number in the workbook’s regional number format.
Recommended Free Tools
Convert a cleaned column without VALUE
- Generate the cleaned text in a helper column.
- Copy the helper results.
- Paste them as values if you no longer need the formulas.
- Select the pasted values and use an appropriate number conversion method, such as changing the column’s data type in Power Query or applying Excel’s available text-to-number conversion.
- Test addition, subtraction, sorting, and number-format changes on several rows.
Be careful with identifiers that contain only digits. A code such as 00123 may need to remain text so its leading zeros survive. Convert to a number only when the field represents a quantity or measure rather than an identifier.
Best Value
- Compatible with Wide Screens - To ensure compatibility with the dual monitor mount, your each monitor must meet three conditions at the same time: First, computer screens size range: 13 to 32 inches. Second, screen weight range: 4.4 to 19.8 lbs. Third, the back of the monitor screen must have VESA mounting holes with a pitch of 75x75mm or 100x100mm.
- Regarding the compatibility with desks - Your desk must meet three conditions at the same time: First, desk material: Only wooden desks are recommended, plastic or glass desks cannot be used. Second, desk thickness range: 0.59" - 3.54". Third, the bottom of the desk should not have any cross beams or panels, as this will interfere with installation. We recommend carefully checking that your desk and monitors meets all above conditions before purchasing.
- Dual C-Clamp Hold - Worried your dual monitors might wobble or slip? Our upgraded base uses a larger platform plus a dual C-clamp structure to lock the dual monitor arm firmly to your desk. Each arm safely keeps your screens steady while you type, click and game—no shaking, no sliding, just a clean and secure setup you can trust every day. It also provides Grommet Mounting installation choice, both options ensure stable and secure fixation for your 0.59" - 3.54" desk.
- Full-Motion Adjustment For Comfortable View - Pull the screen closer when you’re deep in a spreadsheet, push it back to watch videos, or rotate to portrait for coding — moving everything smoothly with just one hand. The monitor stand offers +85°/-50° tilt, ±90° swivel and 360° rotation. Raise your monitor up to 15.75″ to support a healthy sitting posture. Whether you’re working from home, gaming through the night, or switching between video calls and documents, getting the screens to your natural line of sight helps relieve neck, shoulder and back strain so you can stay focused longer with less fatigue.
- Keep Your Desk Organized: By lifting both screens off the desktop, this dual monitor stand opens up valuable space for your keyboard, notebook, docking station or a simple, clutter-free work area. Built-in cable management guides wires along the arms, keeping cords out of sight and out of the way. Enjoy a tidy, modern workstation that looks as good as it feels to use.
Common failure modes and fixes
| Symptom | Likely cause | Fix |
|---|---|---|
=TRIM(A1) leaves a visible gap |
The gap is a nonbreaking space, code 160 | Use TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) |
| Find and Replace reports zero replacements | The character is not ASCII space 32, or the wrong range is selected | Check the range and test for CHAR(160); use a formula or Power Query |
| Words run together unexpectedly | SUBSTITUTE removed every space, including meaningful ones | Use TRIM or the targeted REGEXREPLACE pattern |
| REGEXREPLACE shows an unknown-function error | The workbook is opened in an unsupported Excel version | Use Find and Replace, helper formulas, or Power Query; verify Microsoft 365 or Excel for the web availability before using regex |
| Numbers still sort alphabetically | The cleaned result is text | Wrap the cleanup in VALUE or change the column type after cleaning |
| Leading zeros disappear | An identifier was converted to a number | Keep the result as text and apply a text format before pasting values |
| Only some rows clean correctly | The source contains different kinds of spacing or punctuation | Inspect character codes, test representative rows, and choose a formula that matches each field’s rules |
A safe workflow for production spreadsheets
- Define the field: decide whether spaces are forbidden, normalized, or meaningful.
- Preserve the source: duplicate the column, worksheet, or query output before modifying it.
- Sample the edge cases: include blank cells, leading and trailing spaces, repeated spaces, nonbreaking spaces, punctuation, accented characters, leading zeros, and already-clean values.
- Select the narrowest method: use SUBSTITUTE for total removal, TRIM for word spacing, REGEXREPLACE for letter-digit boundaries, Find and Replace for a one-time range, and Power Query for recurring imports.
- Validate the result: compare row counts, check for unexpected blanks, test lookups and duplicate detection, and confirm that numeric fields calculate correctly.
- Publish deliberately: keep formulas when the process must remain transparent, or paste values only when a downstream system requires static text.
This sequence prevents the most damaging mistake: applying a technically correct replacement to a field whose spaces carry meaning.
Worked examples
Compact product codes
If column A contains ZX 400, LM 08, and Q 7, place =SUBSTITUTE(A2," ","") in B2 and fill down. The results are ZX400, LM08, and Q7. Keep column B as text if these are identifiers; converting them to numbers would discard letters and may alter leading-zero segments.
Descriptions with accidental spacing
If A2 contains High Capacity Battery , =TRIM(A2) returns High Capacity Battery. It removes the outside padding and reduces the internal run to one space without concatenating the words.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteImported code with a nonbreaking space
If a copied value looks like AB 123 but ordinary SUBSTITUTE does not remove the gap, use =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) to normalize it to AB 123. If the field forbids all spaces, apply the all-space-removal version after that normalization.
Mixed text that needs selective removal
If A2 contains Room 4B, the regex formula returns Room 4B because the space is between a word and a digit but the digit is followed by a letter? The targeted alternatives detect only a letter immediately on one side and a digit immediately on the other; in 4B, there is no space between the digit and letter to remove. A value such as Room 4 B also keeps the space after 4 because it is followed by a letter but is preceded by a space, not directly by the digit at the boundary being matched. Test the exact code grammar rather than assuming every space near a number is unwanted.
Quick Recap
Method selection checklist
- Every space is invalid: use
SUBSTITUTEor a selected-range replacement. - Words must stay readable: use
TRIM. - Copied data contains invisible characters: normalize
CHAR(160), runCLEAN, then trim. - Only letter-number separators are invalid: use the REGEXREPLACE pattern in a supported Microsoft 365 or Excel for the web workbook.
- The correction happens once: use Find and Replace after selecting a tightly bounded range.
- The source refreshes: create a Power Query replacement step.
- The result must calculate: use VALUE or change the column type, but preserve identifiers as text.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
