How do i eliminate ? / & Data > 100,000 rows

  • Hii!
    I am looking for a solution regarding eliminating of characters in a column.

    There are 3 columns in a worksheer
    SerialNo,Mobile number and text.

    In the Mobile number column it should contain only 12 digit numbers alone neither it should have length < 12 nor &gt; 12 and only number should be allowed in this columns.
    how do i sort such columns when the column has around 1,00,000 rows of data.
    with mix of different characters [like -,),( ]?
    could any1 provide me the solution.


  • How do i open a txt file which has more than 1,50,000 rows in Excel.
    when i open through excel i get and error and i am able to open around 66000(approx),the remaining records are not shown?


  • You can extract data with more than 65536 rows into Excel. Only limitation shall be the data shall be in a new worksheet. If it is ok with you, do let me know. I shall be sending you the code.

    Probably you are extracting this text data from a commercial software!!1

    Ram P:cheers:

  • With that amount of data I recommend using Access (since that program is created to handle large amount of data, and Excel isn't)


  • You can use the following code to import data with more than 65536 rows into Excel. After import, You can use the text-column feature in Excel to make the data the way you want to .

    Good Luck :cheers:

    Sub ImportText_file()
    Dim ResultStr As String
    Dim FileName As String
    Dim FileNum As Integer
    Dim Counter As Double
    FileName = InputBox("Please enter the Text File's name with its path, e.g. c:\test.txt")
    'If Error Then End
    If FileName = "" Then End
    FileNum = FreeFile()
    Open FileName For Input As #FileNum
    Application.ScreenUpdating = False

    Workbooks.Add template:=xlWorksheet
    Counter = 1
    Do While Seek(FileNum) < LOF(FileNum)

    Application.StatusBar = "Importing Row " & _
    Counter & " of text file " & FileName
    Line Input #FileNum, ResultStr
    If Left(ResultStr, 1) = "=" Then
    ActiveCell.Value = "'" & ResultStr

    ActiveCell.Value = ResultStr
    End If

    If ActiveCell.Row = 65536 Then
    ActiveCell.Offset(1, 0).Select
    End If
    Counter = Counter + 1

    Application.StatusBar = False

    End Sub

  • This should help you, a little UDF, place in standard module, you can see the characters to remove, so you can edit at will

    Kind regards

    Jack in the UK


    Public Function CleanandRemove(eName As String)

    Dim i As Integer

    Const Remove = "-,),(]?"
    For i = 1 To Len(Remove)

    eName = Application.WorksheetFunction. _
    Substitute(eName, Mid(Remove, i, 1), "")

    Next i

    CleanandRemove = eName

    End Function

  • Quote

    Originally posted by somasekar_n
    how do i sort such columns when the column has around 1,00,000 rows of data.

    hold on a sec....

    is that 10,000 (ten thousand)

    or 100,000 (one hundred thousand)

    or 1,000,000 (one million)

    rows of data ?

  • Hi guys,

    Obviously the amount of data is the bottle-neck here.

    The easiest solution to sort is to create a userform and use the spreadsheet component in the Web Office Component. The WOC are shipped with Office 2000 and above.

    Copy all data to the component - sort - and then copy all data back.

    The provided solution for importing data in the referred thread is not good due to the time it takes.

    Please let me know if someone wants me to provide solutions for above.

    We still not have any answer from the OP in the referred thread...

  • Hii!
    thanks to all who have replied to my query and i am yet to apply the solutions given by group members and
    lambuhere1,jack in uk&gt; thx for ur codes
    chris&gt; well 1,00,000 is 100,000(hundred thousand rows


  • Sekar,

    Since these two posts seem to be linked, I have merged them into one OK. Hope you don't mind.

    When you get a chance to test the replies to date, please feel free to post back to this merged thread.

    Regards, :thumbup:

  • hey y'all...i am brand new...i am just curious, i think that that code mentioned above would be extremely useful...however, i am a novice with excel but i am forced to use other statistical packages like spss if i want to import more than the 65000 records that excel can hold..

    my question is, where would i write that code to allow me to import more than 65K records into excel??????

    i know the question probably sounds stupid but i am a novice....any commentary or advice would be helpful..thank you in advance..have a good day!!!!:bouncy:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!