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.

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

hi
not sure if you can do this with a format but this might work for you
=IF(LEN(A12)4,LEFT(A12,3) & " " &RIGHT(A12,2),LEFT(A12,2)&"_ "&RIGHT(A12,2))
that wrapped. it's all one line. i was confused by what you ment by...
DD__MX so i put a space and an underscore. replace it with what you need.
Adjust ranges to fit your data. copy down. copy formulas and paste a values.

Regards
FSt1
"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.

"carl" wrote in message
...
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.

Try this:
=IF(LEN(A1)=5,LEFT(A1,3),LEFT(A1,2)&" ")&" "&RIGHT(A1,2)

Hi,

Assuming that you have text with length of 5 and 4, please find the
following formula.

=IF(LEN(A1)=5,CONCATENATE(LEFT(A1,3),"
",RIGHT(A1,2)),CONCATENATE(LEFT(A1,2)," ",RIGHT(A1,2)))

Regards,
Soundar.

"FSt1" wrote:

hi
not sure if you can do this with a format but this might work for you
=IF(LEN(A12)4,LEFT(A12,3) & " " &RIGHT(A12,2),LEFT(A12,2)&"_ "&RIGHT(A12,2))
that wrapped. it's all one line. i was confused by what you ment by...
DD__MX so i put a space and an underscore. replace it with what you need.
Adjust ranges to fit your data. copy down. copy formulas and paste a values.

Regards
FSt1
"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.

I think this will work for you:
=LEFT(A3,2) & " " & RIGHT(A3,2)


--
RyGuy


"Stephen" wrote:

"carl" wrote in message
...
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.

Try this:
=IF(LEN(A1)=5,LEFT(A1,3),LEFT(A1,2)&" ")&" "&RIGHT(A1,2)

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

I misread. Try this instead:

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

Of course, unless you use a fixed-space font, the text will not "line up".
--ron

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