Use formatted text instead of number value...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rod Janson - 05 Sep 2006 12:40 GMT

Hi,

I have a column with formatted leading zero integer values like this:

12345678910
00123456789

and I need to insert a space in this string like this:

123456 78910
001234 56789

I tried to use this function,

=MID(F1;1;6)&" "&MID(F1;7;5)

however it interprets cells as integer values and not the cells' formatted
text values.

How can I in my formula use the cells' actual displayed formatted text
instead of the integer value?

Thanks a lot for hints on this

regards

Rod

Reply to this Message

Stefi - 05 Sep 2006 12:54 GMT

If column F was formatted like Text, your formula worked for me!

Regards,
Stefi

„Rod Janson” ezt írta:

> Hi,
>
[quoted text clipped - 23 lines]
>
> Rod

Reply to this Message

Pete_UK - 05 Sep 2006 14:44 GMT

If your values are numbers, then you will have to add the leading zeros
in your formula.Try this:

=LEFT(REPT("0";11-LEN(F1))&F1;6)&" "&RIGHT(F1;5)

This assumes that you will not have more than 6 leading zeros - if you
may have more, then use this variation:

=LEFT(REPT("0";11-LEN(F1))&F1;6)&" "&RIGHT(REPT("0";11-LEN(F1))&F1;5)

The formula can be copied down the column.

Hope this helps.

Pete

> Hi,
>
[quoted text clipped - 23 lines]
>
> Rod

Reply to this Message

Tushar Mehta - 05 Sep 2006 14:51 GMT

If you would rather keep it as a number and just format for display
purposes, use the custom format 000000 00000

Signature

Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

> Hi,
>
[quoted text clipped - 23 lines]
>
> Rod

Reply to this Message