Has anyone seen characters that look like spaces but aren't?

DavidStank

New Member
Joined
Jan 5, 2016
Messages
15
A problem I've encountered lately is importing data from online sources and coming across characters that look like spaces but aren't recognized by Excel as spaces. This is entirely new to me.
For example, in the formula bar I can click the cursor before a leading letter character and backspace or delete the leading 'mystery character' but expressions such as =IF(LEFT(C1,1)=" "...) returns a value of FALSE every time.
Also, if I delete the mystery character and then strike the space bar to insert a space in its place, expressions similar to the example above will work as expected.
Does anyone know of a formula or function for dealing with these characters or converting them to spaces?
Thanks for any guidance on dealing with these characters.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
You need to get the code of that character. Paste it to sheet and fire formula: =CODE(A1)
It will return the code of a character. After that you can check against it (in this case, we check space): =IF(LEFT(C1)=CHAR(32),...
 
Upvote 0
A problem I've encountered lately is importing data from online sources and coming across characters that look like spaces but aren't recognized by Excel as spaces.

They might be non-breaking spaces (HTML nbsp; ASCII 160). Although you might write IF(LEFT(A1,1) = CHAR(160),...), it would be prudent to replace NBSPs with regular spaces. Enter the following in a parallel column:

=SUBSTITUTE(A1, CHAR(160), " ")

Then copy the parallel column and paste-value to overwrite the original data in column A.
 
Last edited:
Upvote 0
The macro at this website will remove all leading/trailing
and multiple interspersed char 32 space characters.
It will also remove and/or convert char 160 non breaking
spaces into standard char 32 space characters. It will
work on text or numbers and the numbers will be
converted to true numeric numbers.

I use this macro dozens of times every single day! It's a real time saver.

Rearranging Data in Columns
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top