trim function code help

hi guys

can someone help me with a function code that i can use to replace the trim function. i'm working on a project that requires me to use all other excel functions except for the trim function and yet i need to remove both trailing and leading spaces from a cell.
i'm fairly new at vba.

all help will be extremely appreciated
thanks in advance

Comments

  • Well, let's say you have 50 leading spaces.... to a 4 letter word. So 50 spaces and then "$h*t". Len(yourWord)=54. You could have something recursively call itself if the first (or last) character is a space.

    So, thisFunc(yourWord) would see
    If Left(yourWord,1) = " " Then
    thisFunc(Right(yourWord,Len(yourWord)-1))
    ElseIf Right(yourWord,1) = " " Then
    thisFunc(Left(yourWord,Len(yourWord)-1))
    Else
    thisFunc = yourWord
    End If

    Haven't tried this, but this is the basic idea. basically, if the first or last character is NOT a space, it's good to go. Otherwise, take off the space you find (beginning or end) and put it through the test again. One of the great examples of recursion creating (complex) simplicity.
  • if you want to access the contents of your cell, myCell.Value in place of yourWord. Also, without using a UserDefinedFunction thisFunc, you can use a Do While loop.

    myTrimmingVar = Cells(1,1).Value
    ' Or
    'myTrimmingVar = Range("A6").Value

    Do While Left(myTrimmingVar,1) = " "
    myTrimmingVar = Right(myTrimmingVar,Len(myTrimmingVar)-1)
    Loop

    Do While Right(myTrimmingVar,1) = " "
    myTrimmingVar = Left(myTrimmingVar,Len(myTrimmingVar)-1)
    Loop

    Should give you similar results without the Function.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion