• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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
0
kingjely
Asked:
kingjely
  • 3
  • 3
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now