Use formatted text instead of number value...

[Rod Janson]

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

 

[Guest]

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

Regards,
Stefi


„Rod Janson†ezt írta:

 

[Pete_UK]

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

 

[Tushar Mehta]

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

--
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