Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2015
    Posts
    7

    Unanswered: split long data in single cell of a row into 5 rows

    Hello Experts,

    I have a data in excel which I use in sql and need to paste in TSO screen. Now the data is only in one cell which is long and it doesnt get pasted in TSO uniformly. So I want this data to be splitted in multiple rows...can anybody tell me if this is possible ?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Is it possible?
    ....almost certainly..
    ...what you could do is write a function which copies & formats the text into a paste buffer.
    You may need to mess around appending CR & LF characters.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2015
    Posts
    7
    Hello healdem,

    Thanks for response. Can you please explain me in a easier way how to do that...or can I have your email so that let me share a file with you.....

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    There's the "Text to Columns" functionality that's built into Excel.
    You can also write a series of formulae in a row that parse the data from the source cell into usable chunks.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jan 2016
    Location
    San Jose
    Posts
    4
    Quote Originally Posted by hverma View Post
    Now the data is only in one cell which is long and it doesnt get pasted in TSO uniformly. So I want this data to be splitted in multiple rows...can anybody tell me if this is possible ?
    "Text to Columns" will let you split one cell into columns fairly easily. From there, just copy > paste-special > transpose, to convert the columns into rows.

  6. #6
    Join Date
    Dec 2015
    Posts
    7
    Quote Originally Posted by avargas View Post
    "Text to Columns" will let you split one cell into columns fairly easily. From there, just copy > paste-special > transpose, to convert the columns into rows.
    - Thanks for the advise. But after the transpose I get those data in diff columns...and I wanted the data to get splitted in one column but in multiple rows.
    So that it becomes easy for me to copy paste into TSO and data gets pasted uniformly there.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So write a function
    Select the cell, then run that function that copies that cell as formatted text into the paste buffer as a macro using a keyboard shortcut.
    To build the skeleton of the macro use the macto recorder
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2015
    Posts
    7
    Quote Originally Posted by healdem View Post
    So write a function
    Select the cell, then run that function that copies that cell as formatted text into the paste buffer as a macro using a keyboard shortcut.
    To build the skeleton of the macro use the macto recorder
    - Thats the problem...I dont know how to write a macro......

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    There are several suggestions in this thread for how to solve your problem that don't involve writing VBA code. Either try one of them and then report back the results, or post some sample data of what you have and what you need it to look like, so that we don't waste our time and yours with unhelpful ideas.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if you are using Excel then it sounds likje no is a fine time to start learning hiow to wrtie macros

    asa said
    use the macro recorder to writ the skeleton of the macro. essentailly the macro recorder records what you do.

    then edit that macro

    so far we do not know what line size you need
    nor do we know what the data you have is
    so we have near feck all chance of proividing any detailed assistance.
    for me Ill gladly help people who make an effort, but in all hinesty I cannot be bothered if people cannot be bothered to make a start themselves

    not knowing what version of excel you are using but this shoudl work in Excel 2003, assuming you have addes the forms library as a VBA reference
    Code:
    Sub splitcell()
    Worksheets("Sheet1").Activate
    Dim FormattedText As String
    Dim TextToProcess As String
    Dim StartAt As Integer
    Dim NoChars As Integer
    
    TextToProcess = ActiveCell.Value
    StartAt = 1
    NoChars = 10
    FormattedText = ""
    While StartAt <= Len(TextToProcess)
      'extract the specified number of character (nochars) starting from from the current position(StartAt) to our new variable
      'add a carriage retuen + line feed to that
      FormattedText = FormattedText & "Line:" & Format((StartAt + NoChars - 1) \ NoChars, "00") & Mid(TextToProcess, StartAt, NoChars) & vbCrLf
      'increment our start postion but the number of characters just extracted
      StartAt = StartAt + NoChars
    Wend
    MsgBox FormattedText
    'the following is stolen from
    'http:/www.thespreadsheetguru.com/blog/2015/1/13/how-to-use-vba-code-to-copy-text-to-the-clipboard
    'PURPOSE: Copy a given text to the clipboard (using DataObject)
    'SOURCE: www.TheSpreadsheetGuru.com
    'NOTES: Must enable Forms Library: Checkmark Tools > References > Microsoft Forms 2.0 Object Library
    Dim obj As New DataObject
    'Make object's text equal above string variable
      obj.SetText FormattedText
    
    'Place DataObject's text into the Clipboard
      obj.PutInClipboard
    
    End Sub
    the above chops the active cell into lines of upto 10 characters and copies to the paste buffer
    Last edited by healdem; 01-29-16 at 14:28.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Dec 2015
    Posts
    7
    Below is the data I have
    input data
    Rome,Tokyo,B200,2900,shanghai,4500,3500,berlin,450 0,

    - I want this to be as below:
    Rome,Tokyo,B200,
    2900,shanghai,4500,
    3500,berlin,4500,

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...so what rule do you use to split your data that way?
    blocks of 3 items each item separated by comma's

    ..if so use the split function to split the source column up and then rebuild the lines as required
    OR
    ...use the instr fucntion to find every third term in a loop and then rebuild the lines as required
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Dec 2015
    Posts
    7
    Actually I did that manually....since I have more that 500 such data to be splitted..so doing it manually will be take a lot time...
    so find out a any formula or trick to do that in a quick way...may the result come out in next sheet or in diff columns on same sheet..

Posting Permissions

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