Data quality

[Anonymous]

Hi All,

i have a column called name. it contain "firstname midname lastname".

Problem is that spaces between "firstname midname lastname" is not even. e.g. first (two spaces) mindname (one space)lastname.

how to make the spaces even(only one space between "firstname midname lastname")

[sunny_talwar · Accepted solution]

May be this

Replace(name, '  ', ' ') as name

[Anonymous]

thanks for your reply. already got the same solution 

[Anonymous]

Hello - you could also try this. 

SubField(Name,'  ',1)&' '&SubField(Name,'  ',2)

[vkhassanov]

For any number of spaces inside:

Left(name,Index(name,' ')-1) & ' ' & Trim(Mid(name,Index(name,' '),Index(name,' ',-1)-Index(name,' '))) & ' ' & Trim(Right(name,Len(name)-Index(name,' ',-1)))