Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text to Column (Excel 2000)

    I want to write a generic macro that will perform a text to column command on any column. I've written the macro to first insert a column to the right, then perform the text to column command, fixed width of 11. The excess text is thrown into the inserted column then that column is deleted. What remains is the column with only 11 characters of data. This is great. My problem is I need this macro to be generic so that it will work on any column. Currently, the macro only wants to work with columns F and G. Here is the macro:
    Sub ColumnWidth()
    '
    Range("G1").Select
    Selection.EntireColumn.Insert
    Range("F1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(11, 1))
    Range("G1").Select
    Selection.EntireColumn.Delete
    Range("F1").Select
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text to Column (Excel 2000)

    This will put the first 11 chars of the cells in the active column into that column

    Steve

    <pre>Sub First11()
    Dim iCol As Integer
    Dim rng As Range
    Dim rCell As Range
    iCol = ActiveCell.Column

    Set rng = Range(Cells(1, iCol), Cells(65536, iCol).End(xlUp))
    For Each rCell In rng
    rCell.Value = Left(rCell.Value, 11)
    Next

    Set rCell = Nothing
    Set rng = Nothing
    End Sub</pre>


  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Column (Excel 2000)

    Thank you. You prevented me from ripping out all of my hair! Worked like a charm. You made my day.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Column (Excel 2000)

    I have a similar need for another column in excel. This new column(s) I want to be 58 characters or less. I need the macro to take the remaining characters and place the data into a column that has been inserted to the right. Here is the hard part - I need the column to stop at 58 characters or less OR the last space between words. I.E - The column heading is: "The Quick Brown Fox Jumped Over the Fence And Ran Down The Street." I need the macro to stop before the word, 'Street', and place that into an inserted column to the right. Thank you.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text to Column (Excel 2000)

    Not entirely clear on what you want/need.

    Could you be more specific about what all the macro is supposed to do?

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Column (Excel 2000)

    The column width can only be 58 characters wide. If there are more characters in the text, I need that to cut off and place the remaining characters in the next column. So if there are 70 characters in the column, I need 58 characters to remain in the column and the next 12 characters to be placed in the next column. However, I did not want a word to be cut in half between columns. So I was wondering if there is a way to check for 58 characters. If the 58th character is within a word, could you back up to the last space before the word and then cut off the text. I know this is strange. Thank you.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text to Column (Excel 2000)

    Try this variation on Steve's code:

    Sub First58()
    Dim iCol As Integer
    Dim rng As Range
    Dim rCell As Range
    Dim iPos As Integer
    iCol = ActiveCell.Column

    Set rng = Range(Cells(1, iCol), Cells(65536, iCol).End(xlUp))
    For Each rCell In rng
    If Len(rCell.Value) > 58 Then
    iPos = InStrRev(Left(rCell.Value, 59), " ")
    rCell.Offset(0, 1).Value = Mid(rCell.Value, iPos + 1)
    rCell.Value = Left(rCell.Value, iPos - 1)
    End If
    Next

    Set rCell = Nothing
    Set rng = Nothing
    End Sub

    Note: it uses InStrRev, which is only available in Excel 2000 and up.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Column (Excel 2000)

    Thank you. Everything you stated made so much sense, but I would not have been able to come up with this complicated code. It worked perfect. Thank you once again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •