insert a dash
in [Programming]
|
Prev: ScreenUpdating
Next: Lookup Table
From: Striker3070 on 5 May 2010 14:00 I have a column of numbers, 5 digit up to 9 digit. I need to insert a dash after the first number of any 5 digit number, and a dash after the second digit if the number is longer than 5 digits. so 12345 would need to become 1-2345 and 1234567 would need to become 12-34567 If my original numbers are in column A, I can make the new ones in Column B
From: Rick Rothstein on 5 May 2010 14:17 Give this formula a try... =REPLACE(A1,3-(LEN(A1)=5),0,"-") -- Rick (MVP - Excel) "Striker3070" <striker3070(a)qwest.net> wrote in message news:37978E49-438C-4175-9F61-8C87A5486857(a)microsoft.com... > I have a column of numbers, 5 digit up to 9 digit. I need to insert a > dash after the first number of any 5 digit number, and a dash after the > second digit if the number is longer than 5 digits. > > so 12345 would need to become 1-2345 > and 1234567 would need to become 12-34567 > > If my original numbers are in column A, I can make the new ones in Column > B
From: Rick Rothstein on 5 May 2010 14:32 And if you were looking for a VB solution, consider looping your cells using a statement construction similar to this... DashedText = Application.Replace(CellText, 3 + (LEN(CellText)=5),0,"-") -- Rick (MVP - Excel) "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:#K8uh9H7KHA.4208(a)TK2MSFTNGP06.phx.gbl... > Give this formula a try... > > =REPLACE(A1,3-(LEN(A1)=5),0,"-") > > -- > Rick (MVP - Excel) > > > > "Striker3070" <striker3070(a)qwest.net> wrote in message > news:37978E49-438C-4175-9F61-8C87A5486857(a)microsoft.com... >> I have a column of numbers, 5 digit up to 9 digit. I need to insert a >> dash after the first number of any 5 digit number, and a dash after the >> second digit if the number is longer than 5 digits. >> >> so 12345 would need to become 1-2345 >> and 1234567 would need to become 12-34567 >> >> If my original numbers are in column A, I can make the new ones in Column >> B >
From: Gary Brown on 5 May 2010 14:56 =if(len(a1)<=5,left(A1,1)&"-"&right(a1,len(a1)-1) ,left(a1,2)&"-"&right(a1,len(a1)-2)) -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Striker3070" wrote: > I have a column of numbers, 5 digit up to 9 digit. I need to insert a dash > after the first number of any 5 digit number, and a dash after the second > digit if the number is longer than 5 digits. > > so 12345 would need to become 1-2345 > and 1234567 would need to become 12-34567 > > If my original numbers are in column A, I can make the new ones in Column B > > . >
From: Bob Umlas, Excel MVP on 5 May 2010 14:57
You can also just format the cells: [<100000]0-0000;00-00000 "Striker3070" wrote: > I have a column of numbers, 5 digit up to 9 digit. I need to insert a dash > after the first number of any 5 digit number, and a dash after the second > digit if the number is longer than 5 digits. > > so 12345 would need to become 1-2345 > and 1234567 would need to become 12-34567 > > If my original numbers are in column A, I can make the new ones in Column B > > . > |