Text formatting question

[Cybertech]

I have a column of numbers formatted as text like this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
010000<o></o>
010101<o></o>
020201<o></o>
020306<o></o>
<o> </o>
I would like them formatted like this:<o></o>
<o> </o>
01.00.00<o></o>
01.01.01<o></o>
02.02.01<o></o>
02.03.06<o></o>
<o> </o>
Is there an easy way to do the conversion? Nothing I try seems to work.<o></o>
<o> </o>
Thanks!<o></o>

 

[Smitty]

Welcome to the Board!

You can select the range, then goto Data-->Text to Columns-->Next-->Next-->Under the Column Data Format options choose Date and the format you want, then finish.

HTH,

 

[T. Valko]

I have a column of numbers formatted as text like this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
010000<o></o>
010101<o></o>
020201<o></o>
020306<o></o>
<o></o>
I would like them formatted like this:<o></o>
<o></o>
01.00.00<o></o>
01.01.01<o></o>
02.02.01<o></o>
02.03.06<o></o>
<o></o>
Is there an easy way to do the conversion? Nothing I try seems to work.<o></o>
<o></o>
Thanks!<o></o>

If you convert them to numeric numbers then you can format them like that.

However, if you want them to remain TEXT entries then you'd need a helper formula to do it.

Are those supposed to be dates?

 

[Cybertech]

Biff,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
They are not dates (they are actually part of a document numbering system) and need to remain as text.<o></o>
<o> </o>
Thanks!<o></o>

 

[T. Valko]

Biff,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
They are not dates (they are actually part of a document numbering system) and need to remain as text.<o></o>
<o></o>
Thanks!<o></o>

Try this in a helper cell:

=TEXT(A1,"00\.00\.00")

 

[Cybertech]

Works Great

Thanks!!!

 

[T. Valko]

Works Great

Thanks!!!

You're welcome. Thanks for the feedback!