Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting a dash into a column of numbers
Hello all. I would like to insert a "-" into a column of numbers after the
5th digit each time. The 1st 5 digits will not always be the same, obviously and there are either 6 or 7 digits total in each number. I was trying to do this via find and replace, but I can't seem to figure out a good way. For example: 7000111 I would like to be 70001-11 Thanks in advance!!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting a dash into a column of numbers
=IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2))
"pm" wrote: Hello all. I would like to insert a "-" into a column of numbers after the 5th digit each time. The 1st 5 digits will not always be the same, obviously and there are either 6 or 7 digits total in each number. I was trying to do this via find and replace, but I can't seem to figure out a good way. For example: 7000111 I would like to be 70001-11 Thanks in advance!!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting a dash into a column of numbers
LEFT(A1,5) instead of LEFT(A1,3)
"ufo_pilot" wrote: =IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2)) "pm" wrote: Hello all. I would like to insert a "-" into a column of numbers after the 5th digit each time. The 1st 5 digits will not always be the same, obviously and there are either 6 or 7 digits total in each number. I was trying to do this via find and replace, but I can't seem to figure out a good way. For example: 7000111 I would like to be 70001-11 Thanks in advance!!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting a dash into a column of numbers
Thanks, but is there a way to account for the fact that there are either 1 or
2 digits to the right of the dash? Thanks for the formulas guys...I'm way farther now than I was "Teethless mama" wrote: LEFT(A1,5) instead of LEFT(A1,3) "ufo_pilot" wrote: =IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2)) "pm" wrote: Hello all. I would like to insert a "-" into a column of numbers after the 5th digit each time. The 1st 5 digits will not always be the same, obviously and there are either 6 or 7 digits total in each number. I was trying to do this via find and replace, but I can't seem to figure out a good way. For example: 7000111 I would like to be 70001-11 Thanks in advance!!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting a dash into a column of numbers
Nevermind, I just sorted by digits and I got it to work by tweaking 1 or 2 in
the formula. Thanks guys! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting a dash into a column of numbers
Something like this:
=IF(LEN(A1)5,LEFT(A1,5)&"-"&RIGHT(A1,LEN(A1)-5),A1&"-") Hope this helps. Pete pm wrote: Thanks, but is there a way to account for the fact that there are either 1 or 2 digits to the right of the dash? Thanks for the formulas guys...I'm way farther now than I was "Teethless mama" wrote: LEFT(A1,5) instead of LEFT(A1,3) "ufo_pilot" wrote: =IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2)) "pm" wrote: Hello all. I would like to insert a "-" into a column of numbers after the 5th digit each time. The 1st 5 digits will not always be the same, obviously and there are either 6 or 7 digits total in each number. I was trying to do this via find and replace, but I can't seem to figure out a good way. For example: 7000111 I would like to be 70001-11 Thanks in advance!!!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inserting a dash into a column of numbers
=LEFT(A1,5)&"-"&MID(A1,6,2)
-- David Biddulph "pm" wrote in message ... Thanks, but is there a way to account for the fact that there are either 1 or 2 digits to the right of the dash? Thanks for the formulas guys...I'm way farther now than I was "Teethless mama" wrote: LEFT(A1,5) instead of LEFT(A1,3) "ufo_pilot" wrote: =IF(LEFT(A1,5)<"",LEFT(A1,3)&"-"&RIGHT(A1,2)) "pm" wrote: Hello all. I would like to insert a "-" into a column of numbers after the 5th digit each time. The 1st 5 digits will not always be the same, obviously and there are either 6 or 7 digits total in each number. I was trying to do this via find and replace, but I can't seem to figure out a good way. For example: 7000111 I would like to be 70001-11 Thanks in advance!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
Consecutive Numbers down a column not to Exceed 49 | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |