Excel Question

Posted on 2009-12-16
Last Modified: 2012-05-08


If i go Data - Text to Columns, and accept,  the 3 columns copy over the 3 next to it.

My spread sheet goes from

   (             Single string                      )    
  ADIDAS,      BOTTOMS,       LADIES,          $290,          5

to This

 ADIDAS,      BOTTOMS,       LADIES,  

IS there a function to INSERT the new columns instead of overWriting them?

Thanks !



I have a feild which i have exported from Crystals reports TO Excel.

The feild is a SINGLE STRING:

 ADIDAS,      BOTTOMS,     LADIES,    

How do i get excel to recognise the commas within the string, and seperate? at the moment it is seperating all commas, but as this is a single string, Excel is reading this as a single string

The reason why, is because i have a customer wanting to export this report to xcel, and stort by each column individually.

THanks for any help
Question by:kingjely
    LVL 8

    Expert Comment

    There is no any option available in 'TextToColumn' which can fulfill your requirement directly. But you may have alternative solution like :
    (1) When apply TextToColumn, you select the destination after the last column from which no data exists.
    (2).You have to write some macro which Inserts columns and then perform the TextToColumn.
    LVL 8

    Author Comment

    Okay got ya.

    Well i thing the layout of the columns is how the customer wants it, as that's how i have Layed it out in crystas for them.

    Would you have any info to write a macro to insert 2 columns? Should I ask that in a seperate question?

    THanks kindly
    LVL 8

    Expert Comment

    No. There is no need to ask a seperate question. I am writing a macro for it and will give you as soon as possible.

    LVL 8

    Author Comment

    Fantastic, I've never looked into macros before, i have heard alot, I will do some research now, so if i can see what you do, then maybe i can understand a little better for next time!

    Muchly appreciated
    LVL 8

    Accepted Solution

    Here is the Macro for you.
    I considerer that the data are seperated by COMMA(,).
    Also see the attached file which contains sample data and also Macro.

    Sub GenerateData()
        Dim ShObj As Worksheet
        Dim ColumnToSplit As Long
        Dim FirstRow As Long
        Dim LastRow As Long
        Dim NoOfColumnsToInsert As Long
        Dim ColumnArr
        Dim RowIndex As Long
        Dim ColIndex As Long
        Set ShObj = ThisWorkbook.Worksheets(1)
        ColumnToSplit = 1
        FirstRow = 1
        LastRow = ShObj.Cells.SpecialCells(xlCellTypeLastCell).Row
        ColumnArr = Split(ShObj.Cells(FirstRow, ColumnToSplit), ",")
        NoOfColumnsToInsert = UBound(ColumnArr)
        For ColIndex = ColumnToSplit To NoOfColumnsToInsert
            ShObj.Columns(ColIndex + 1).Insert
        ShObj.Range(ShObj.Cells(FirstRow, ColumnToSplit), ShObj.Cells(LastRow, ColumnToSplit)).TextToColumns
    End Sub

    LVL 8

    Author Closing Comment

    HI Thanks very much
    This gives me something to work with,
    I'll just have to work out what to do with!

    Thanks for your help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now