splitting a column by 35 character maximum each column

I have the InvTitle column ...that is varchar(200)
I need to split to different columns with 35 characters each as maximum allowed. So, I used Substring function  to get the required resultset as shown below:

Please use this #test1 to see the resultset
--------------------------------------------------------------------------------------------------------------------------------------
Create table #test1(InvTitle varchar(200))
Insert #test1 values
('Stacy Abrams')
,('Robert L. & Patricia D. Sheehan JTWROS')
,('Robert E. johnson Irrevocable GST Trust-Matthew E. johnson Managing Trustee')
,('William A. honey Trust, dated 6/9/95')
,('Henry Warren stanckler Cresent Interest Irrevocable Trust Dtd. 11/19/78 - Henry Warren Strickler, Trustee')

select * from #test1

select  substring(INVTITLE,1,35) as Title_1
                  , substring(INVTITLE,36,35) as Title_2
                  , substring(INVTITLE,71,35) as Title_3
                  , substring(INVTITLE,106,35) as Title_4
                  , substring(INVTITLE,141,35) as Title_5 from #test1

-------------------------------------------------------------------------------------------------------------------------------
the Resultset I get is:


Title_1                                                           Title_2                                                            Title_3                                     Title_4                         Title_5
Stacy Abrams
Robert L. & Patricia D. Sheehan JTW      ROS
Robert E. johnson Irrevocable GST T      rust-Matthew E. johnson Managing Tr   ustee
William A. honey Trust, dated 6/9/9      5
Henry Warren stanckler Cresent Inte      rest Irrevocable Trust Dtd. 11/19/7           8 - Henry Warren Strickler, Trustee
 
 
 
What I need:

      Registration lengths will need to be corrected for the title if it has more than 35 characters in title.
      Each column of the Title allows for 35 character maximum.   In the sample above from data file, the column name Title_2 appears to be cut in middle of a word.  Data should be cut at a different point (e.g character 32) to reflect complete word; it may be different for different Invtitle.


Any ideas???
Jay RAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
Try this
Sub resplitatwords()
Dim cel As Range
For Each cel In Range("A2:A" & Range("A2").End(xlDown).Row)
    If Len(cel) >= 35 Then
        col = 0
        Do While Len(cel.Offset(, col)) >= 35
            cel.Offset(, col).Select
            lng = Len(cel.Offset(, col)) - Len(WorksheetFunction.Substitute(cel.Offset(, col), " ", ""))
            lng = WorksheetFunction.Substitute(cel.Offset(, col), " ", "|", lng)
            lng = InStr(lng, "|")
            lft = Left(cel.Offset(, col), lng)
            rgt = Right(cel.Offset(, col), Len(cel.Offset(, col)) - lng)
            cel.Offset(, col) = lft
            cel.Offset(, col + 1) = rgt & cel.Offset(, col + 1)
            col = col + 1
        Loop
    End If
Next cel
End Sub

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I need to split to different columns with 35 characters each as maximum allowed.
Drawing a blank on this one.  Is 'Contact the source of this data and ask them why the eff you can't provide a varchar(200)' an option?
0
 
Jay RAuthor Commented:
yeah...tried asking them why. They are of not any help:(...my bad luck!
Need to figure out a way to do this either in sql or I have to manually edit the entire excel sheet with almost >2000 records manually:(

Am also open to any other suggestions of fixing this thing in excel ...rather than manually doing it by going through each single row. Any ideas?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Jay RAuthor Commented:
Thanks Jim for quick reply.
So...there is no way to fix this in SQl???....my bad:(
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In T-SQL if there is it's not coming to me, but perhaps there are experts that know either a full-text search or some kind of custom solution.

So ... I'll back out gracefully to encourage those experts to respond.
0
 
Saqib Husain, SyedEngineerCommented:
Can you upload an excel file with only a few more (say 10) lines of data and on another sheet show what is the expected result?
0
 
Jay RAuthor Commented:
Hi Syed,
                  I am attaching the CurrentResultset and DesiredResultset ...in excel sheet.
CurrentResultset.xlsx
DesiredResultset.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
Try this macro
Sub resplitatwords()
Dim cel As Range
For Each cel In Range("A2:A" & Range("A2").End(xlDown).Row)
    If Len(cel) >= 35 Then
        col = 0
        Do While Len(cel.Offset(, col)) >= 35
            cel.Offset(, col).Select
            lng = Len(cel.Offset(, col)) - Len(WorksheetFunction.Substitute(cel.Offset(, col), " ", ""))
            lng = WorksheetFunction.Substitute(cel.Offset(, col), " ", "|", lng)
            lng = InStr(lng, "|")
            lft = Left(cel.Offset(, col), lng)
            rgt = Right(cel.Offset(, col), Len(cel.Offset(, col)) - lng)
            cel.Offset(, col) = lft
            cel.Offset(, col + 1) = rgt & cel.Offset(, col + 1)
            col = col + 1
        Loop
    Else
        Stop
    End If
Next cel
End Sub

Open in new window

0
 
Jay RAuthor Commented:
Hi Syed.....Thanks very much. am kind of new to the macros. So, how do I add & execute this code in the excel sheet.
0
 
Saqib Husain, SyedEngineerCommented:
Right-click on the sheet tab name
Select View code
From the menu      Insert > Module
Paste this code in the VBA window
Close the VBA window
Press Alt-F8
Select the macro name
Click on run
0
 
Jay RAuthor Commented:
Hi syed...Thanks for the help.
Have a Quick question...after I hit run...it goes to the debugger window with highlighted on stop...so should I run it again??
0
 
Jay RAuthor Commented:
So, do I have to click 12 times Run to get the DesiredResultset???
0
 
Saqib Husain, SyedEngineerCommented:
Sorry, just delete that line. I inserted it for checking.
0
 
Jay RAuthor Commented:
But after I just deleted that line...am getting a syntax error.

sorry...am not familiar with Excel Macros. So...not sure how to fix this syntax error.
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.