On Fri, 16 Nov 2007 11:35:32 -0500, Ron Rosenfeld
wrote:

On Fri, 16 Nov 2007 08:16:07 -0800, carl
wrote:

Raw Formated
HPQEI HPQ EI
HPQBV HPQ BV
DDMX DD MX
EMCPE EMC PE


I am trying to convert (reformat) ColA to ColB. The right 2 characters need
to be separated from the other characters by a space AND the left characters
need to have a length of 3. For example, for row 3 (DDMX), I need the
formated value to be DD__MX.

Thank you in advance.

=LEFT(A1,LEN(A1)-2) & " " & RIGHT(A1,2)
--ron

That correction should have been:

=LEFT(A1,MIN(3,LEN(A1)-2)) & REPT( " ",6-MIN(5,LEN(A1)))& " " & RIGHT(A1,2)


--ron