In a column, for 3400 rows, How do I get rid of an unwanted space preceding
the sentence and make sure the first letter of the sentence is capitalized.

This formula should do the trick.

=UPPER(LEFT(TRIM(A1)))&RIGHT(TRIM(A1),LEN(TRIM(A1) )-1)

enter it in a unused column, Copy down and then Copy-Paste Special Values
back to column A
--
If this helps, please remember to click yes.


"LeisaA" wrote:

In a column, for 3400 rows, How do I get rid of an unwanted space preceding
the sentence and make sure the first letter of the sentence is capitalized.

THANK YOU SO MUCH! Have a great day!

"Paul C" wrote:

This formula should do the trick.

=UPPER(LEFT(TRIM(A1)))&RIGHT(TRIM(A1),LEN(TRIM(A1) )-1)

enter it in a unused column, Copy down and then Copy-Paste Special Values
back to column A
--
If this helps, please remember to click yes.


"LeisaA" wrote:

In a column, for 3400 rows, How do I get rid of an unwanted space preceding
the sentence and make sure the first letter of the sentence is capitalized.

Assuming what you asked is all that you want to do, then give this macro a
try...

Sub FixSentences()
Dim X As Long, LastRow As Long, CellText As String
Const FirstRow As Long = 2
Const DataColumn As String = "A"
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
For X = FirstRow To LastRow
CellText = Cells(X, DataColumn).Value
CellText = LTrim(CellText)
Mid(CellText, 1, 1) = UCase(Left(CellText, 1))
Cells(X, DataColumn).Value = CellText
Next
End Sub

--
Rick (MVP - Excel)


"LeisaA" wrote in message
...
In a column, for 3400 rows, How do I get rid of an unwanted space
preceding
the sentence and make sure the first letter of the sentence is
capitalized.

On Feb 16, 12:46*pm, LeisaA wrote:
In a column, for 3400 rows, How do I get rid of an unwanted space preceding
the sentence and make sure the first letter of the sentence is capitalized.


Sub test()
For Each c In Range("A2:A3400")
c.Value = Trim(c.Value)
c.Value = UCase(Left(c.Value, 1)) & Right(c.Value, Len(c.Value) -
1)
Next
End Sub