*** 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.

 

[mumps]

OK but first I need to know how you want your data organized. Can you please provide answers to my questions in Post# 7? Please explain in detail.

 

[kakhils]

Dear I have around 400 to 500 numbers like this in a text file and for every number i want the same.. what u did in post#5.
These are in same sheet from a1 to a400 depending on numbers i have in my text file.

Thanks.

 

[mumps]

I understand that but I still need to know how you want to organize the lists generated for each number. For example, if you have the following numbers, you will generate 9 number lists. Do you want each list in a separate column? Do you want then underneath each other in one column? Do you want them on the same worksheet or on a different worksheet?
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

 

[kakhils]

If it is same column.. than its awesome.. otherwise it can done in various colums.. cause it will create huge data.

 

[mumps]

This macro will place the lists in a separate column starting in column B.

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lCol As Long
    Dim rngEnd As String
    Dim rng1 As String
    Dim rng2 As String
    Dim x As Long
    Dim Num As Range
    For Each Num In Range("A1:A" & LastRow)
        lCol = ActiveSheet.UsedRange.Columns.Count
        rngEnd = Split(Num, "-")(2)
        rng1 = Split(Num, "-")(0) - 1
        rng2 = Split(Num, "-")(1)
        For x = 1 To rng2 - rng1
            Cells(x, lCol + 1) = rng1 + x & " " & rngEnd
        Next x
    Next Num
    Application.ScreenUpdating = True
End Sub

Keep in mind that if you have a very long list of numbers it may take a while to run the macro.

 

[kakhils]

This helped... but if i take large data it gives "Subscript out of range" with rngEnd = Split(Num, "-")(2) yellow marked..

 

[kakhils]

This code helped but it is impossible to copy all data back to text one by one column wise...

Is is possible to do it on 1 column. if i paste my data row wise.. i.e. first text in a1, second in b1 and so on...and get the result in one column..??

Thanks.

 

[mumps]

Just to clarify .... You want all the data in one column. Is that correct?

 

[kakhils]

Yes you are right...

 

[mumps]

If your numbers are in column A, this macro will replace those numbers with the updated numbers deleting your original data.

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rngEnd As String
    Dim rng1 As String
    Dim rng2 As String
    Dim x As Long
    Dim Num As Range
    For Each Num In Range("A1:A" & LastRow)
        rngEnd = Split(Num, "-")(2)
        rng1 = Split(Num, "-")(0) - 1
        rng2 = Split(Num, "-")(1)
        For x = 1 To rng2 - rng1
            Cells(Range("B" & Rows.Count).End(xlUp).Row + 1, "B") = rng1 + x & " " & rngEnd
        Next x
    Next Num
    Columns(1).EntireColumn.Delete
    Rows(1).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

If you want to keep your original data, then this macro will put the updated numbers in column B, leaving your original data in column A.

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rngEnd As String
    Dim rng1 As String
    Dim rng2 As String
    Dim x As Long
    Dim Num As Range
    For Each Num In Range("A1:A" & LastRow)
        rngEnd = Split(Num, "-")(2)
        rng1 = Split(Num, "-")(0) - 1
        rng2 = Split(Num, "-")(1)
        For x = 1 To rng2 - rng1
            Cells(Range("B" & Rows.Count).End(xlUp).Row + 1, "B") = rng1 + x & " " & rngEnd
        Next x
    Next Num
    Range("B1").Delete
    Application.ScreenUpdating = True
End Sub

Keep in mind that the maximum number of rows that Excel 2007 and later versions support is 1,048,576. Depending on how many numbers you have in column A, if the updated numbers exceed the maximum, you will have a problem.