If and Left function together

[kamiljaku]

Hello everyone,

Im trying to extract just the left hour value from the string with the Left function but dont know why it doesnt work. Sometimes there are just 5 positions to substract and sometimes 6.

I tried with the following Formula with many combinations

=IF(LEFT(A1;1)="1";LEFT(A1;4);LEFT(A1;5))

9:30-- 18:00
11:30- 20:00

and recive

9:30
11:3

I dont understand why it ist working with 11:30.

 

[Scott T]

11:30 is 5 characters long, 9:30 is 4 characters long
Try

=IF(LEFT(A1;1)="1";LEFT(A1;5);LEFT(A1;4))

 

[kamiljaku]

I've tryed every combination. Anyway when I use you formula it shows me

9:3
11:

Just as it would't take the string lenght serious.

 

[AlanY]

or you would try

=TRIM(LEFT(SUBSTITUTE(A1;"-";REPT(" ";500));500))

 

[Scott T]

The formula by AlanY should work better.

 

[kamiljaku]

Thank you. I need to learn how this funktion works. Thanks again

 

[Scott T]

 =TRIM(LEFT(SUBSTITUTE(A1;"-";REPT(" ";500));500))

The formula changes the first "-" to 500 spaces. Then takes the first 500 characters. the trim function gets rid of the extra spaces giving the time.

 

[admiral100]

Hi,

Another way :

=LEFT(A1,FIND("-",A1)-1)

 

[kamiljaku]

 =TRIM(LEFT(SUBSTITUTE(A1;"-";REPT(" ";500));500))

The formula changes the first "-" to 500 spaces. Then takes the first 500 characters. the trim function gets rid of the extra spaces giving the time.

Thanks for the explanation. And what if I would like to change the second "-" instead? I assume it is not to be done with the LEN function?

 

[admiral100]

I noticed that you are using a semicolon instead of a comma...sorry

=LEFT(A1;FIND("-";A1)-1)

And for your secoend "-" try this :

=TRIM(LEFT(SUBSTITUTE(A1;"-";REPT(" ";500);2);500))