Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a dash
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a dash
Give this formula a try...
=REPLACE(A1,3-(LEN(A1)=5),0,"-") -- Rick (MVP - Excel) "Striker3070" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a dash
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" wrote in message ... Give this formula a try... =REPLACE(A1,3-(LEN(A1)=5),0,"-") -- Rick (MVP - Excel) "Striker3070" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a dash
=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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a dash
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert a dash
hi
since you posted in programming, perhaps a VB solution..... Sub insertdash() Dim r As Range, c As Range Set r = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row) For Each c In r If Len(c) 5 Then c.Value = Left(c, 2) & "-" & Right(c, Len(c) - 2) Else c.NumberFormat = "@" 'in case xl can interpret as a date c.Value = Left(c, 1) & "-" & Right(c, Len(c) - 1) End If Next c End Sub regards FSt1 "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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If I have a list of numbers, how do I insert a dash in each one? | Excel Discussion (Misc queries) | |||
How insert a dash in the third position of a column with data? | Excel Discussion (Misc queries) | |||
insert a dash | Excel Programming | |||
Need insert a dash in between last 4 digits in text string | Excel Worksheet Functions | |||
Shortcut Key to insert an em-dash? | Excel Programming |