Unwanted space between letters in a word

cazfromoz

New Member
Joined
Nov 8, 2011
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
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

New Member
Joined
Nov 8, 2011
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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.
 

cazfromoz

New Member
Joined
Nov 8, 2011
Messages
46
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Cheers and thanks Brian for your input.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
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. :(
 
Last edited:

cazfromoz

New Member
Joined
Nov 8, 2011
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:

Forum statistics

Threads
1,136,868
Messages
5,678,224
Members
419,753
Latest member
Vj3006

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
Top