When is a space not a space?

[mdavid]

Hi, I had a problem using mid(x, i, 1) to detect spaces in a cell - it wasn't recognizing what appeared to be a space. After using "debug.print Asc(Mid(x, i, 1)) " I discovered that what appeared to be a space had an Asc value of 160, so my question is should I now search for Asc 32 and Asc 160 or how can I ensure what appears to be a space is a space (Asc 32).

Thanks for any help
David

 

[AliGW]

Ascii 160 is not a space according to the chart linked below, however Ascii 255 is )a non-breaking space):

ASCII code Non-breaking space or no-break space, American Standard Code for Information Interchange, The complete ASCII table, characters,letters, vowels with accents, consonants, signs, symbols, numbers non, breaking, space, no, break, space,ascii,2

ASCII code Non-breaking space or no-break space, American Standard Code for Information Interchange, ASCII table, characters, letters, vowels, consonants, signs, symbols, 20240213

theasciicode.com.ar

 

[GraH - Guido]

Actually CHAR(160) is the non-breaking-space character, which is used extensively on the web. (https://www.ascii.cl/htmlcodes.htm)


In an Excel formula CLEAN() will get rid of it, so I'm assuming it would also work in VBA with clean(x).

Your question

should I now search for Asc 32 and Asc 160 or how can I ensure what appears to be a space is a space (Asc 32)

cannot be answered by us. As it depends on what you want to accomplish. Perhaps simulate a find and replace in which you substitute char(160) with char(32)?

 

[Chihiro]

Typically, first step of data analysis is cleaning data and transforming it for analysis. In fact, for data analysts, I'd say majority of your work is in setting up ETL (Extract, Transform, Load) process to ensure data consistency for analysis.

This includes removing non-standard character(s), trimming space(s), etc.

There are many ways to deal with this, depending on tool set that you are using for analysis.

• Use VBA - Load data as array, iterate over it and perform clean up/transform operation. Or use Range.Replace()
• Use Formula - Add Helper Columns and use SUBSTITUTE(), TRIM(), CLEAN() etc to perform cleanup.
• Use Get & Transform - Set up query steps to clean up texts (i.e. Custom Function for TRIM, Text.Replace etc)
• Use 3rd party tools/script - Prep data before accessing it with Excel using R, Python etc

Exact method will depend on how you are loading data to Excel and how it's used/organized in Excel.
I'd recommend uploading sample workbook with enough raw data and few manually created output to demonstrate your need.

 

[mdavid]

Thanks for all your help, penny dropped when I heard non-breaking space and remembered  .
Thanks Chihiro, for all the tips, problem is half the time you don't know what you're looking for.