Microsoft Excel - split numbers thats 11-15 digits long
Asked By bill boch
14-Dec-10 10:14 AM
I have numbers that range from 11 to 15 digits long
ex:
123456789123456
12345678912
1234567891234
12345678912345
I need a dash between the first 5 digits then the next 3
12345-678-9123456
12345-678-912
Is there a formula that i can use..
Thanks Bill
Jackpot . replied to bill boch
Try this...(they no longer remain as numbers)
=LEFT(A1,5)&"-"&MID(A1,6,3)&"-"&MID(A1,9,10)
bruce mcpherson replied to bill boch
Hello
this should do it
=LEFT(A1,5)&"-"&MID(A1,6,3)&"-"&RIGHT(A1,LEN(A1)-8)
Bruce
bill boch replied to bruce mcpherson
hi bruce
I tried this in macro but it won't work
bruce mcpherson replied to bill boch
Hello
when you say 'in macro' what do you mean? . This is just and excel formula and can be entered directly into an excel cell.
bruce
Jackpot . replied to bill boch
If you are looking for a macro; select the range and run this
Sub Macro()
Dim cell As Range
For Each cell In Selection
If cell <> "" Then
cell.Value = Left(cell.Value, 5) & "-" & Mid(cell.Value, 6, 3) & "-" & Mid(cell.Value, 9)
End If
Next
End Sub
bill boch replied to bruce mcpherson
sorry I thought it was a marco
I tried cut and paste and a box came up and said microsoft office excel cannot calculate a formula. cell references in the forula's result, creating a circular reference
bill boch replied to Jackpot .
all map numbers are in column A
bruce mcpherson replied to bill boch
hello
if the input data is in column A, and you put the formula in column B, there should not be a circular reference.
perhaps you could paste the columns for me
bruce
Jackpot . replied to bill boch
OK. Try the below macro.
Sub Macro()
Dim cell As Range
For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If cell <> "" And IsNumeric(cell) Then
cell.Value = Left(cell.Value, 5) & "-" & Mid(cell.Value, 6, 3) & "-" & Mid(cell.Value, 9)
End If
Next
End Sub
bill boch replied to bruce mcpherson
got it sorry i was putting forula in same column ....Thanks for your help you were very helful
thank you for being so understanding
Bill
Microsoft Excel Microsoft Excel (full name Microsoft Office Excel ) is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X . It features calculation, graphing tools, pivot tables and a macro
Microsoft Excel Microsoft Excel (full name Microsoft Office Excel ) is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X . It features calculation, graphing tools, pivot tables and a macro
Macro Excel Excel Vorrei convertire macro excel in openoffice cal grazie Excel - Italian Discussions Excel (1) Macro (1) Excel Vorrei (1) Database (1) Informazioni (1) Il messaggio (1) Gamberini (1) Microsoft (1
CHANGE CASE COMMAND IN MICROSOFT EXCEL Excel Microsoft Word contains the command of CHANGE CASE which is very useful, but Microsoft Excel does not contain this command due to which its too much difficult as rewrite the whole content of a cell. Cant this command be available in Microsoft Excel too, I think its not hard for Microsoft Corporation. Or if this command is available
Opening / Closing files using Excel Macro? Excel Excel 2007 on XP Pro: I am trying to write a a macro in Excel which will: (1) open a workbook from another only if not already open (2) close the workbook from another only if the other workbook is already open. Any ideas? Excel Programming Discussions MsoAutomationSecurityByUI (1) Excel 2007 (1) Error (1) Application.AutomationSecurity (1) Excel (1) Application.FileDialog (1) MsoFileDialogFilePicker (1) Workbook