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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Jay RAuthor Commented:
Thanks Jim for quick reply.
So...there is no way to fix this in SQl???....my bad:(
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.