#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default =Text

I have a long column of ref numbers, each cell needs to be converted
to text. So i am using =TEXT (B2,0) - (with the result I then copy
and paste as special)

This works fine .... up to a point. As I also need the ref number to
have 8 digits so a number that starts as 6 digits needs to be padded
with two zeros at the start ie 585859 needs to end up as 00585859.

Can this adjustment be made in the original =TEXT statement, if so how
or what is the best way to deal with this problem.

With thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 913
Default =Text

On Mon, 6 Jul 2009 07:23:25 -0700 (PDT), Bob
wrote:

I have a long column of ref numbers, each cell needs to be converted
to text. So i am using =TEXT (B2,0) - (with the result I then copy
and paste as special)

This works fine .... up to a point. As I also need the ref number to
have 8 digits so a number that starts as 6 digits needs to be padded
with two zeros at the start ie 585859 needs to end up as 00585859.

Can this adjustment be made in the original =TEXT statement, if so how
or what is the best way to deal with this problem.

With thanks



Try this:

=TEXT(B2,"00000000")

Hope this helps / Lars-Åke
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default =Text


Try:

=TEXT (B2,Rept(0,8))


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112942

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default =Text

On 6 July, 15:29, NBVC wrote:
Try:

=TEXT (B2,Rept(0,8))

--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile:http://www.thecodecage.com/forumz/member.php?userid=74
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=112942


Thanks NBVC exactly waht I was after.

Bob
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t zzxxcc Excel Worksheet Functions 2 August 26th 08 11:04 PM
grab cell text from multi-tab workbook, show text in another workb pfa Excel Worksheet Functions 16 August 10th 07 08:50 PM
Text does not display in "Text boxs" and when wrapping text in a c Esteban Excel Discussion (Misc queries) 1 March 9th 07 12:59 AM
merged cells into one text cell, size varies dependant on text dat Jazzylady825 Excel Discussion (Misc queries) 0 December 9th 05 09:26 PM
text (3750 char.)truncates with text wrap and row heigh adjusted? Boydster Excel Discussion (Misc queries) 1 May 19th 05 05:59 PM


All times are GMT +1. The time now is 09:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"