Excel Formatting: Cleaning up information so it looks consistent

[cocolete]

Hello Community,

I'm trying to clean up a database and have come along with certain issues. The data base has information from multiple system and each system has a different way of formatting the text. In order to send it out in a professional consistent format I've cleaned the data as mucha as I can. Here are my issues:

1. How can I take data and turn all UPPER case to lower case letters but maintain the 1st letter in UPPER CAP

2. I lot of the data has many spaces in between (ex. Cpn 6% 3/15 mat) how can I ensure that there is only one space between words and numbers. By this I mean to ensure that if I have a cpn 6.875 that it doesnt tak and do this 6. 875 because of the dot.

MAW TEST.xls

Thank You

[Russell Dawson]

In B1 input =PROPER(A1)

Drag it down

[Fermented Reptile]

Hi there,

To get the first letter capitalized and to remove superfluous spaces, try:

=PROPER(TRIM(A1))

HTH,

Fermented R

[Russell Dawson]

Much better

[cocolete]

Thank you very much guys that was great!

[BarryTSL]

You could use this code in a module to save having an extra column.

Sub macro1()

Range("A65536").End(xlUp).Select
ii = ActiveCell.Row

        Do Until ActiveCell.Row = 1
Range("A" & ii).Select
aaa = ActiveCell.Value
bbb = Application.WorksheetFunction.Proper(aaa)
ccc = Application.WorksheetFunction.Trim(bbb)
ActiveCell.Value = ccc
ii = ii - 1
        Loop

End Sub