Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add leading zero's to number to make char count = 6 ?
Hi,
All the cells in Column B in a worksheet must be 6 chars long. If I have a string 123 in cell A1 using VBA how would I populate cell B2 with this value including leading Zero to fill the gap to 6 characters. Column A could contain a number with anything between 1 and 6 characters. So for example the result should be A B 1 123 000123 2 52 000052 3 52174 052174 Thanks Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add leading zero's to number to make char count = 6 ?
Hi Paul,
All the cells in Column B in a worksheet must be 6 chars long. Columns("B:B").NumberFormat = "000000" If I have a string 123 in cell A1 using VBA how would I populate cell B2 with this value including leading Zero to fill the gap to 6 characters. To maintain a dynamic link, try: Range("B2").FormulaR1C1 = "=RC[-1]" O, to copy the static value, try: Range("B2").Value = Range("A2").Value --- Regards, Norman "Paul" <paulm dot c @ iol dot ie wrote in message ... Hi, All the cells in Column B in a worksheet must be 6 chars long. If I have a string 123 in cell A1 using VBA how would I populate cell B2 with this value including leading Zero to fill the gap to 6 characters. Column A could contain a number with anything between 1 and 6 characters. So for example the result should be A B 1 123 000123 2 52 000052 3 52174 052174 Thanks Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add leading zero's to number to make char count = 6 ?
Just use formulas
B1: = TEXT(A1,"000000") -- HTH RP (remove nothere from the email address if mailing direct) "Paul" <paulm dot c @ iol dot ie wrote in message ... Hi, All the cells in Column B in a worksheet must be 6 chars long. If I have a string 123 in cell A1 using VBA how would I populate cell B2 with this value including leading Zero to fill the gap to 6 characters. Column A could contain a number with anything between 1 and 6 characters. So for example the result should be A B 1 123 000123 2 52 000052 3 52174 052174 Thanks Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add leading zero's to number to make char count = 6 ?
Bob:
Is there a way of formatting the numbers to get the leading zeros and leave them as numbers (not text)?? -- Gary's Student "Bob Phillips" wrote: Just use formulas B1: = TEXT(A1,"000000") -- HTH RP (remove nothere from the email address if mailing direct) "Paul" <paulm dot c @ iol dot ie wrote in message ... Hi, All the cells in Column B in a worksheet must be 6 chars long. If I have a string 123 in cell A1 using VBA how would I populate cell B2 with this value including leading Zero to fill the gap to 6 characters. Column A could contain a number with anything between 1 and 6 characters. So for example the result should be A B 1 123 000123 2 52 000052 3 52174 052174 Thanks Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add leading zero's to number to make char count = 6 ?
No, because as soon as you format to number, Excel will strip those leading
zeroes. -- HTH RP (remove nothere from the email address if mailing direct) "Gary's Student" wrote in message ... Bob: Is there a way of formatting the numbers to get the leading zeros and leave them as numbers (not text)?? -- Gary's Student "Bob Phillips" wrote: Just use formulas B1: = TEXT(A1,"000000") -- HTH RP (remove nothere from the email address if mailing direct) "Paul" <paulm dot c @ iol dot ie wrote in message ... Hi, All the cells in Column B in a worksheet must be 6 chars long. If I have a string 123 in cell A1 using VBA how would I populate cell B2 with this value including leading Zero to fill the gap to 6 characters. Column A could contain a number with anything between 1 and 6 characters. So for example the result should be A B 1 123 000123 2 52 000052 3 52174 052174 Thanks Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add leading zero's to number to make char count = 6 ?
Looks like:
Format Cells Number Custom 000000 will work -- Gary's Student "Paul" wrote: Hi, All the cells in Column B in a worksheet must be 6 chars long. If I have a string 123 in cell A1 using VBA how would I populate cell B2 with this value including leading Zero to fill the gap to 6 characters. Column A could contain a number with anything between 1 and 6 characters. So for example the result should be A B 1 123 000123 2 52 000052 3 52174 052174 Thanks Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add leading zero's to number to make char count = 6 ?
Yes, but that is just look of the data. From the OP, I read that as wanting
the actual string in B. -- HTH RP (remove nothere from the email address if mailing direct) "Gary's Student" wrote in message ... Looks like: Format Cells Number Custom 000000 will work -- Gary's Student "Paul" wrote: Hi, All the cells in Column B in a worksheet must be 6 chars long. If I have a string 123 in cell A1 using VBA how would I populate cell B2 with this value including leading Zero to fill the gap to 6 characters. Column A could contain a number with anything between 1 and 6 characters. So for example the result should be A B 1 123 000123 2 52 000052 3 52174 052174 Thanks Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading zero's trail Number | Excel Worksheet Functions | |||
Leading Zero's in Excel | Excel Discussion (Misc queries) | |||
Leading Zero's | Excel Discussion (Misc queries) | |||
How to add leading zero's | Excel Worksheet Functions | |||
Leading zero's | Excel Discussion (Misc queries) |