Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    moving text from a multi to single column (Excel 97)

    I think I want to know an easy way to copy multiple columns of text to a single column.

    I have some large text files exported from my library catalogue program which contain the keywords. I want to be able to print out a list of the keywords and the number of occurences of each one.
    The original txt file has keywords separated by "; ". eg
    blast furnace; coke size; HGI;
    Romax; coke size
    HGI; blast furnace;CSR; minerals

    After importing into Excel, with the separator ";" each row contains the keywords from a single library record, variable number of keywords per record (row) and variable number of rows.
    Eg above would be in cells A1, B1, C1, A2, B2, A3, B3, C3, D3 and columns B, C, etc begin with a space.

    If it is all in a single column (with the leading spaces trimmed off), then I can use Pivot Table to produce the list which I then copy to Word so I can format to use minimum pages. So far, doing it manually has taken me days.
    Can anyone suggest an easier way?

    Thanks and g'day
    Ruth

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: moving text from a multi to single column (Excel 97)

    Not quite clear what you are trying to do, but will making column E =TRIM(A1)&TRIM(B1)&TRIM(C1)&TRIM(D1) do what you want?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: moving text from a multi to single column (Excel 97)

    I am not sure if I understand you correctly either , but if you want to convert the spreadsheet to a single column;
    then select all of the used rows/columns, paste it into word as a table.
    Then convert the table to text, using paragraph markers as the separator.

    This will quickly give you one long column.
    If you then want unique entries, paste back into excel and use the advanced filter, etc.

    I hope it helps

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: moving text from a multi to single column (Excel 97)

    The VBA routine below should do what you are asking:

    <pre>Public Sub ConvertToColumn()
    Dim I As Long, J As Long, K As Long, lMaxRow As Long, lMaxCol As Long
    Application.ScreenUpdating = False
    ActiveSheet.Range("A1").EntireColumn.Insert (xlShiftToRight)
    lMaxRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 2
    lMaxCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 2
    K = 0
    For I = 0 To lMaxRow
    For J = 1 To lMaxCol
    If Trim(ActiveSheet.Range("A1").Offset(I, J).Value) <> "" Then
    ActiveSheet.Range("A1").Offset(K, 0).Value = Trim(ActiveSheet.Range("A1").Offset(I, J).Value)
    K = K + 1
    End If
    Next J
    Next I
    ActiveSheet.Range("A1").EntireColumn.AutoFit
    Application.DisplayAlerts = False
    Range(ActiveSheet.Range("B1"), ActiveSheet.Range("A1").Offset(lMaxRow, lMaxCol)).Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: moving text from a multi to single column (Excel 97)

    Thank you Legare. It works wonderfully. (after I removed the < in the IF statement - I guess that was some sort of continuation of line thingo?)

    To Kieran: sorry for unclearness. I want to end up with one keyword per cell (ie per row); I started off with the concatenation you had; thanks anyway.

    To Michael: I tried it via Word - but it's too bulky - between 1 and 80 keywords per record, over 17,000 records, and 8 kinds of keywords (files). My computer/Word/Excel combination just couldn't handle it.

    Thanks again everyone
    RuthC

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: moving text from a multi to single column (Excel 97)

    The If statement should have had a less than sign immediately followed by a greater than sign ("<>") which means "not equal to". It should have looked like this:

    <pre> If Trim(ActiveSheet.Range("A1").Offset(I, J).Value) <> "" Then
    </pre>


    Can you show me what you changed? If you removde the less than sign in the above, it should not work properly.
    Legare Coleman

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: moving text from a multi to single column (Excel 97)

    My line looks like:
    If Trim(ActiveSheet.Range("A1").Offset(I, J).Value) > "" Then

    so I guess I haven't any cells less-than "" , since my totals checked out from the original to the final. I will change it back to not-equal-to so that the next time it will definitely work. [Ishould have realised <> but my brain is obviously out of macro-mode <img src=/S/smile.gif border=0 alt=smile width=15 height=15>] Thanks
    Ruth C

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: moving text from a multi to single column (Excel 97)

    Well, you were doing a greater than comparison between two strings and I really don't know what VBA does with that. I am surprised that it worked.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: moving text from a multi to single column (Excel 97)

    Hi Legare

    Just FYI, the following extract from Microsoft Office 2000/Visual Basic Programmer's Guide :

    Comparing Strings by Using Comparison Operators

    Since you're actually comparing ANSI values when you compare two strings, you can use the same comparison operators that you would use with numeric expressions

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: moving text from a multi to single column (Excel 97)

    My question was what it does when the strings are of different lengths, and particularly when one or both of the strings is null.
    Legare Coleman

Posting Permissions

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