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?

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

yovation wrote:
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?

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.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"yovation" wrote:

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?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"yovation" wrote in message
ups.com...
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?

Thank you for your replies.

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


On Sep 26, 10:26 am, JW wrote:
Just incorporate another Substitute formula:
=LOWER(SUBSTITUTE(LEFT(D2,3)," ","")&TRIM(CLEAN(SUBSTITUTE(F2,"
",""))))&RIGHT(D2,2)

yovation wrote:
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?