LEFT(D2,3) but no spaces

[yovation]

Hi,

I am using the formula below in (E2)

=LOWER(LEFT(D2,3)&TRIM(CLEAN(SUBSTITUTE(F2," ",""))))&RIGHT(D2,2)

The problem I am having is... If one of the first 3 characters in D2
is a space, I don't want it. I need the formula to just ignore a
space if it is there. It can either drop the space and get the first
2 characters, or ignore the space and pick up the first 3 characters
that are not spaces.

Please, does anyone have a solution?

 

[JW]

Just incorporate another Substitute formula:
=LOWER(SUBSTITUTE(LEFT(D2,3)," ","")&TRIM(CLEAN(SUBSTITUTE(F2,"
",""))))&RIGHT(D2,2)

 

[Guest]

Put an example of the text you have on D2 and what you have on F2 and what
you would like to see as a result, not what you are currently getting. This
way I may be able to give you an accurate answer.

 

[Don Guillett]

Does this help?
=LEFT(TRIM(f2),3)

 

[yovation]

Thank you for your replies.

JW, that is exactly what I was trying to do. It worked perfectly.