*** Urgent help. *** Take Data from text file and output in text file

[kakhils]

Take 102201631000-102201633999-234245 from text file and take right formula from first two numbers =right(text1,6) & right(text2,6). i.e. 631000 & 633999 then i need to place them in text like.

102201631000 234245
102201631001 234245
102201631002 234245
102201631003 234245
till
102201633999 234245

in .txt output keeping text as fixed.

 

[DLB20720]

I assume you only have the '102201631000-102201633999-234245' text to start with? Is that correct?

 

[kakhils]

Yes.. But like that I have hundreds of text.. To be converted.. Please help

 

[DLB20720]

I'm still not sure I follow this. The examples you gave of what you want the final results to look like make it appear as though that initial text defined a range of 102201631000 234245 to 102201633999 234245. If that is the case, I have one possible solution that would work if you only need to do this one time. If you have hundreds of these number arrangements, that would be better solved through VBA.

Insert your number (102201631000-102201633999-234245) in cell A1
Insert this formula in B1 - =LEFT(A1,12)*1
Insert this formula in C1 - =RIGHT(A1,6)
Insert this formula in D1 - =CONCATENATE(B1," ",C1) That will give you the first number in your range
Insert this formula in B2 - =B1+1
Insert this formula in C2 - =C1
At that point you just copy down the formulas through row 3000 and it will give you the results you want. Depending on what you are doing with this, I would copy and paste the results in column D as Values to eliminate the formulas.

 

[mumps]

Assuming that your number is in cell A1, the following macro will place your list in column A:

Sub Test()
    Application.ScreenUpdating = False
    Dim rngEnd As String
    Dim rng1 As String
    Dim rng2 As String
    Dim x As Long
    rngEnd = Split(Range("A1"), "-")(2)
    rng1 = Split(Range("A1"), "-")(0) - 1
    rng2 = Split(Range("A1"), "-")(1)
    For x = 1 To rng2 - rng1
        Cells(x, "A") = rng1 + x & " " & rngEnd
    Next x
    Application.ScreenUpdating = True
End Sub

 

[kakhils]

Thanks.... it helped.. but i have numerous numbers.. like this..
102201906000-102201911999-23451
108140595000-108140595199-83782
108141172100-108141172199-82638
108141260700-108141260899-27382
211140221000-211140222499-92761
211140282000-211140283999-96254
307142557000-307142561999-27495
307142609900-307142609999-82648
307142680000-307142680899-92736
and many more,...
if you can make something which fetch above mentioned data from text file and give that output in text only.. that will be be a great help ...
Many many thanks for giving your time,.

 

[mumps]

If you have many numbers, assuming they are in column A, you will get a very long list for each number. How do you want these lists of number organized? Do you want them on a different sheet or on the same sheet? Do you want each list of numbers in a separate column? Please clarify.

 

[kakhils]

Dear I have this code.
Sub Macro1()




On Error Resume Next


StartTime = Now()
Dim objFSO, objFolder, objFile, strFileName, strExtension
'Dim a(1048500, 6)
Dim arr(3000)
Dim fs, a, f, mis
Dim maf As Double

Set fs = CreateObject("Scripting.FileSystemObject")
stroutpath = Range("C5").Value


'a.WriteLine ("Mobile_No,Total_Current_Charge,Current_charges_with_Tax,Monthly_Access_Fee,Outgoing_National_Calls,Outgoing_International_Calls,MOC_-_SMS_Messages,Roaming_SMS,Roaming_Incoming,Roaming_OGC,GPRS_Vol_Home,Roam_GPRS_Usage,Jinny_RBT,Call_Fwd_Calls,Excise_Duty,VAT")
If Sheet1.Range("C4").Value = "" Then
MsgBox "Enter File Path"
Exit Sub
End If
strdirpath = Range("C4").Value


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strdirpath)

For Each objFile In objFolder.Files

If InStr(1, objFile.Name, ".txt") > 0 Then
strFileName = UCase(objFile.Name)
Else: strFileName = ""
GoTo a
End If

ReadFile = strdirpath & "\" & strFileName
Const ForReading = 1, ForWriting = 2, ForAppending = 3


Set a = fs.CreateTextFile(stroutpath & "\" & strFileName, True)
''Set b = fs.CreateTextFile(stroutpath & "\" & strFileName, True)


Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, ForReading, TristateFalse)




'ReadNextLine = False
Do While fin.AtEndOfStream <> True
readata = fin.readline
writedata = IIf(InStr(1, readata, "M,") > 1, Mid(readata, 1, InStr(1, readata, ",") - 1), "")


If Len(writedata) > 11 Then
a.writeline (writedata)
End If




Loop


a:
Next
tt = Format(Now() - StartTime, "HH:MM:SS")
MsgBox "Complete Time taken " & tt
End Sub

It only seperates 108140595000-108140595199
108140595000
108140595001
till
108140595199
for every text numbers.

But i want it to KEEP on FIX text at last of every text.

 

[mumps]

My apologies but I don't have any experience working with text files so I find much of the code you posted difficult to follow. I can possibly help to create the list of numbers that you want in Excel but that is as far as I can help unfortunately.

 

[kakhils]

Please give me some code ... i will copy all text data in excel then.. i will again save them in text using your code..