| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert stored numbers into a set naming convention
I have a list of around 2000 numbers ranging from 3 to six digits. I need to
convert all numbers to six digits with leading zeros is 345 becomes 000345 and 12345 becomes 012345. I also need to convert all of those to a set naming convention with a set prifix letter M and followed by PC001 so 345 becomes M000345PCOO1 AND 12345 BECOMES M012345PC001. All original numbers are stored as numbers on one column Can anyone help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert stored numbers into a set naming convention
I'm not sure how you would do this without a lengthy IF statment that counts
the number of digits in your number. Some of the real experts will undoubtably have the proper solution for you. You can get the numbers to show up with the leading zeros by going: format....cells....custom and then in the type: box type in 000000, this will get your numbers to show leading zeros however I don;t know how to carry that formatting over into your formula which would look like this: ="M"&B1&"PC001" This formula will put the M and the PC001 in the proper places but it seems to strip the leading zeros even if your number is formatted to have them. "Sean" wrote: I have a list of around 2000 numbers ranging from 3 to six digits. I need to convert all numbers to six digits with leading zeros is 345 becomes 000345 and 12345 becomes 012345. I also need to convert all of those to a set naming convention with a set prifix letter M and followed by PC001 so 345 becomes M000345PCOO1 AND 12345 BECOMES M012345PC001. All original numbers are stored as numbers on one column Can anyone help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert stored numbers into a set naming convention
="M"&TEXT(A1,"000000")&"PC001"
Biff "tim m" wrote in message ... I'm not sure how you would do this without a lengthy IF statment that counts the number of digits in your number. Some of the real experts will undoubtably have the proper solution for you. You can get the numbers to show up with the leading zeros by going: format....cells....custom and then in the type: box type in 000000, this will get your numbers to show leading zeros however I don;t know how to carry that formatting over into your formula which would look like this: ="M"&B1&"PC001" This formula will put the M and the PC001 in the proper places but it seems to strip the leading zeros even if your number is formatted to have them. "Sean" wrote: I have a list of around 2000 numbers ranging from 3 to six digits. I need to convert all numbers to six digits with leading zeros is 345 becomes 000345 and 12345 becomes 012345. I also need to convert all of those to a set naming convention with a set prifix letter M and followed by PC001 so 345 becomes M000345PCOO1 AND 12345 BECOMES M012345PC001. All original numbers are stored as numbers on one column Can anyone help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert stored numbers into a set naming convention
Brilliant - works like a dream
"Biff" wrote: ="M"&TEXT(A1,"000000")&"PC001" Biff "tim m" wrote in message ... I'm not sure how you would do this without a lengthy IF statment that counts the number of digits in your number. Some of the real experts will undoubtably have the proper solution for you. You can get the numbers to show up with the leading zeros by going: format....cells....custom and then in the type: box type in 000000, this will get your numbers to show leading zeros however I don;t know how to carry that formatting over into your formula which would look like this: ="M"&B1&"PC001" This formula will put the M and the PC001 in the proper places but it seems to strip the leading zeros even if your number is formatted to have them. "Sean" wrote: I have a list of around 2000 numbers ranging from 3 to six digits. I need to convert all numbers to six digits with leading zeros is 345 becomes 000345 and 12345 becomes 012345. I also need to convert all of those to a set naming convention with a set prifix letter M and followed by PC001 so 345 becomes M000345PCOO1 AND 12345 BECOMES M012345PC001. All original numbers are stored as numbers on one column Can anyone help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert stored numbers into a set naming convention
You're welcome. Thanks for the feedback!
Biff "Sean" wrote in message ... Brilliant - works like a dream "Biff" wrote: ="M"&TEXT(A1,"000000")&"PC001" Biff "tim m" wrote in message ... I'm not sure how you would do this without a lengthy IF statment that counts the number of digits in your number. Some of the real experts will undoubtably have the proper solution for you. You can get the numbers to show up with the leading zeros by going: format....cells....custom and then in the type: box type in 000000, this will get your numbers to show leading zeros however I don;t know how to carry that formatting over into your formula which would look like this: ="M"&B1&"PC001" This formula will put the M and the PC001 in the proper places but it seems to strip the leading zeros even if your number is formatted to have them. "Sean" wrote: I have a list of around 2000 numbers ranging from 3 to six digits. I need to convert all numbers to six digits with leading zeros is 345 becomes 000345 and 12345 becomes 012345. I also need to convert all of those to a set naming convention with a set prifix letter M and followed by PC001 so 345 becomes M000345PCOO1 AND 12345 BECOMES M012345PC001. All original numbers are stored as numbers on one column Can anyone help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Numbers stored as text prevent calculations | Excel Worksheet Functions | |||
| Numbers stored as text | Excel Discussion (Misc queries) | |||
| Convert numbers that have hidden Quotation Mark Embedded | Excel Discussion (Misc queries) | |||
| How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
| I enter numbers and they are stored as text | Excel Discussion (Misc queries) | |||
Linear Mode
