| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change a 1,2,3 or 4 digit number to a 6 character text string
One way:
="02"&TEXT(A1,"0000") Copy down as required. Hope this helps. Pete On Mar 28, 7:41*pm, Steve D wrote: Hi, I have a file that has a column with either 1,2,3 or 4 digit numbers. I need to convert that value to a 4 digit number and add '02 to the front of it. For example if A1 = 34 I would like B1 to be equal to '020034, if A1 = 134 then B1 should be '020134. Here is the formula I tried to use but it keeps converting the number into a text string. How can I make this work? =IF(FIXED(LEFT(A1,4),0)<10,"02000" & LEFT(A1,1),IF(FIXED(LEFT(A1,4),0)<100,"0200" & LEFT(A1,2),IF(FIXED(LEFT(A1,4),0)<1000,"020" & LEFT(A1,3),"02" & LEFT(A1,4)))) -- Thank You, Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
| Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
| Counting number of time a character appears in a string | Excel Worksheet Functions | |||
| want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
| want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
Hybrid Mode
