• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to add the Excel function TRIM to a line of VBA code?

Eloise T

Active Member
I want to insert the TRIM function into the line of code below so the date will not have any leading or trainling spaces (ASCII 32).

The line:

If .NumberFormat <> "mm/dd/yyyy" Then .NumberFormat = TRIM("mm/dd/yyyy")

is found in the 8th row of code below. There's no error message given but it doesn't remove any spaces.


Code:
Dim lRow As Long, iColumn As Integer, lRowest As Long

    lRow = 3
    iColumn = 7
    With Ws
        lRowest = .Cells(.Rows.Count, iColumn).End(xlUp).Row

        Do While lRow <= lRowest

            With .Cells(lRow, iColumn)

                If .NumberFormat <> "mm/dd/yyyy" Then .NumberFormat = TRIM("mm/dd/yyyy")

                     .HorizontalAlignment = xlCenter
                     .VerticalAlignment = xlCenter
                     .WrapText = False
                     .Orientation = 0
                     .AddIndent = False
                     .IndentLevel = 0
                     .ShrinkToFit = False
                     .ReadingOrder = xlContext
                     .MergeCells = False

                lRow = lRow + 1
            End With
        Loop
    End With

Thanks for your assistance.
 
Last edited:
Not necessary as the number format not contains any space
and a number format can't be trimmed, a value yes !
As an Excel true date, a real date, a right date is a number
so it can't be trimmed like any wrong date string but I won't guess
as nothing is better than a crystal clear explanation
and an attachment accordingly …
Do you at least open the help for the VBA function Trim ?
 
I used the following line. I didn't get an error message but it didn't work. Spaces were still left in the date cell.

See Ln 112 in macro (file uploaded):
If .NumberFormat <> "mm/dd/yyyy" Then .NumberFormat = TRIM ("mm/dd/yyyy")
 

Attachments

  • Chandoo - VBA TRIM.xlsm
    80.7 KB · Views: 7
Last edited:
Eloise T
As written in #2 Reply
You are 'trim'ing text "mm/dd/yyyy" ... which do not have any spaces - okay?

TRIM-function:
Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
https://support.office.com/en-us/article/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9

You have get those 'not nice datevalues' somewhere.
There You should use that TRIM-function, not with .Numberformat
 
I don't like your answer, but I understand.

Is there a way to temporarily convert the date so it's TRIMable and then put it back into a number?
 
Last edited by a moderator:
Spaces were still left in the date cell.
Of course 'cause there are not Excel dates but just text !
NumberFormat working only with real dates as numbers

From Excel basics, tips to check if dates are real dates or poor text :

• enlarge the column and setup it without any horizontal alignment :
real dates as numbers are right aligned and
wrong dates as text are left aligned …

• Use a very beginner level formula via the function ISNUMBER :
TRUE result means a date and FALSE means a text so very not a date …

• Use the keyboard combo Ctrl 3 Ctrl " :
only real dates are displayed as numbers …
(Same combo to go back to normal display.)
 

Since 2007 version the option is in the formula tab,
in the formulas audit section : display formulas or combo Ctrl "
As the double quote is on the same key as 3 on my local keyboard,
my previous post updated … You can also check wrong dates via function ISTEXT …
 
Since 2007 version the option is in the formula tab,
in the formulas audit section : display formulas or combo Ctrl "
As the double quote is on the same key as 3 on my local keyboard,
my previous post updated … You can also check wrong dates via function ISTEXT …
Good to know re: ISTEXT. I'll look for details on www.excelfunctions.net

The # is above my 3 on my local keyboard.
 
Back
Top