Unwanted space between letters in a word

[cazfromoz]

Hello all

I have utilized the formula =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) which has worked perfectly for a large messy data set except for those text strings that have an unwanted space between the letters in the one word. eg Tree Top Pro perty where the word should be property and S uper where the word should be Super

I changed the Char component in the formula to 32 but still no success.

Any assistance would be very much appreciated.

Thank you
CazfromOz

 

[BrianJN1]

Hello all

I have utilized the formula =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) which has worked perfectly for a large messy data set except for those text strings that have an unwanted space between the letters in the one word. eg Tree Top Pro perty where the word should be property and S uper where the word should be Super

I changed the Char component in the formula to 32 but still no success.

Any assistance would be very much appreciated.

Thank you
CazfromOz

So you are in the Real Estate business?
I understand that formula is not part of a macro but written into a separate column and then copied down.

Presumably your issue is with words/terms pertinent to your industry.

Is there an industry list like a "dictionary" or glossary of terms (without definitions) to which your sheet might reference? That would mean building some kind of macro to cross-reference and maybe prompt to accept a change. That would seem to the logical solution, if such a list exist, but how to get there is beyond me.

 

[cazfromoz]

Hello Brian

Thank you for taking the time to respond. It is no particular industry just people who attend events who are recorded in two columns with column a headed names (both first and last) and column b contains the company name. It is a source data input simple error but not one I can control.

Hope this makes my query a little clearer.

Thanks.

 

[BrianJN1]

My regrets, I can't think of anything else.

 

[cazfromoz]

Cheers and thanks Brian for your input.

 

[Peter_SSs]

If you look back to the original data at an example like Tree Top Pro perty
In that particular example the apparent spaces are in positions 5, 9 and 13 (I think).
Can you ascertain what the character code is for each of those apparent spaces?
And do that for a few different data samples.
That is just in case (I'm being extremely hopeful here I think ) that the mid-word space characters may be different to the between-word space characters.


Edit: One other long-shot. In your one example each 'real word' begins with a capital letter and the errant bit ("perty") does not. Is that consistent?

Edit again: On re-reading post #3 , I doubt either of my hopes will be fulfilled.

 

[cazfromoz]

Hello Peter

The spaces inside a word string are completely random. They appear in a single, two, and three word strings. Your will have multiple entries for the one company with just one with the additional single space.

Not sure if this additional information helps, but it may just be a text to column solution then a concate back together through a filtering process. I guess I was just hoping there was a way more efficient way to do it. I am a complete novice at VBA so I struggle with VBA as a solution. I notice that you are from SYD hope your footy code team made the finals.

Cheers and thanks.
CazfromOZ

 

[Peter_SSs]

Not sure if this additional information helps, ...

I'm afraid not, it didn't answer my questions.

If you look back to the original data at an example like Tree Top Pro perty
In that particular example the apparent spaces are in positions 5, 9 and 13 (I think).
Can you ascertain what the character code is for each of those apparent spaces?


Edit: One other long-shot. In your one example each 'real word' begins with a capital letter and the errant bit ("perty") does not. Is that consistent?