Excel Question


fURTHER TO MY LAST qUESTION,

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

My spread sheet goes from

eg
   (             Single string                      )    
 BRAND      DEPARTMENT    GENDER        VALUE     UNITS
  ADIDAS,      BOTTOMS,       LADIES,          $290,          5


to This

BRAND      DEPARTMENT    GENDER    
 ADIDAS,      BOTTOMS,       LADIES,  


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

Thanks !

Jason


---



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
Jason
LVL 8
kingjelyAsked:
Who is Participating?
 
VipulKadiaConnect With a Mentor Commented:
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
    Next
           
    ShObj.Range(ShObj.Cells(FirstRow, ColumnToSplit), ShObj.Cells(LastRow, ColumnToSplit)).TextToColumns
   
End Sub


SplitData.xls
0
 
VipulKadiaCommented:
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.
0
 
kingjelyAuthor Commented:
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
Jason
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
VipulKadiaCommented:
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.

Thanks.
0
 
kingjelyAuthor Commented:
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
0
 
kingjelyAuthor Commented:
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
Jason
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.