How do i TRIM only from the beginning of a cell?

[CaptainCsaba]

Hey Everyone!

I created a macro a few months ago that help us to make some reports easier to input. There are multiple lines in these responses that we don't need and the only way to find the ones we do need I used the following method. All of the cells that we needed started with a number, had multiple spaces (between 6 and 10) and ended with text. So I created a code that offsets all the cells that begin with a number and have at least 4 spaces after that ("# "). And it worked perfectly but recently the reports got a bit different. All cells have at least 20 spaces at the beginning and it screws up the macro (later it does stuff that gets messed up because of this). So what was "apple" before is now " apple". I can't use the TRIM function because it removes the spaces between the numbers and the text and after that we won't have anything to offset.

So my question is: What code should I use in VBA (or formula) that removes all the spaces but only from the beginning of a cell?

 

[njimack]

If Left(str1, 1) = " " Then str1 = Right(str1, Len(str1) - 1)

 

[steve the fish]

Heres a formula:

=MID(A1,FIND(LEFT(TRIM(A1)),A1),LEN(A1))

 

[Norie]

In VBA you can use the LTrim function to trim spaces from the beginning.

 

[CaptainCsaba]

Worked perfectly, thank you!