Split string in 2 column

By -
I have a field called "Location" in this field we are storing "City, State". Now we want to show city in other column and state in other column. Basically I want to split the "Location" column based on "," (comma).

To achieve this goal I am using the functions:

LEFT
RIGHT
SEARCH
LEN
IF
ISERROR
TRIM

Let’s see how can we do this with use of above given functions.

If Errors, I am using ISERROR formula
=IF (ISERROR (currentFormula), "", currentFormula)

I am going to create a calculated column called "City" and copy & paste the "=IF(ISERROR(LEFT(Location,(SEARCH(",",Location,1)-1))), "", TRIM(LEFT(Location,(SEARCH(",",Location,1)-1))))" in the formula text box. This formula will check whether any error is coming or not if error is coming then it will return blank value otherwise show us "City Name"


I am going to create a calculated column called "State" and copy & paste the "=IF(ISERROR(RIGHT(Location,(LEN(Location)-SEARCH(",",Location,1)))), "", TRIM(RIGHT(Location,(LEN(Location)-SEARCH(",",Location,1)))))" in the formula text box. This formula will check whether any error is coming or not if error is coming then it will return blank value other wise show us "State Name"

Comments

sappai said…
If there are multiple values would these forumlas work

Ex
Value1,value2,value3,value4,value5,
April 16, 2014 at 4:57 AM
Post a Comment

Popular Posts

SharePoint Interview Questions and Answers

By -
SharePoint Interview Questions and Answers Get More Questions and Answers Q What is SharePoint? Ans SharePoint is a browser based document collaboration platform given by Microsoft. Q What are features of SharePoint 2010? Ans Some features are : ·          Document Collaboration ·          Enterprise Search FAST Search ·          New Enhance Web Part ·          Ready made Silver Light web part ·          Business Connectivity Services ·          Social Media Investments ·          Large lists ·          Enhanced collaboration features ·          Visio Services ·          Usage reporting and logging ·          Better Network Differencing & SharePoint Offline in SharePoint Workspace ·          High Availability/ Disaster Recovery Innovation ·          Admin Insights through the Logging & Usage database, and dev dashboard ·          Service Applications ·          SharePoint Designer Enh
Read more

Download Infopath Form Templates

By -
Click here to download InfoPath Form Collection   Hi, I have lots of InfoPath forms with me which are downloaded from lots of places. I am uploading this InfoPath form collection for all InfoPath and SharePoint Users. These form are related to Infopath 2003, 2007 & 2010.   Click here to download InfoPath Form Collection
Read more

How to get current logged user information using JavaScript ?

By -
This is post which is simple and not really needed. But when I started writing the code in ECMAScript I have faced problems in getting the logged in user information. So, my readers may feel good after see this post and really most the users are looking for this too. By this time, we have really understood all about ECMAScript Client Object Model and debugging. So, it's easy for us to know it well. Please find the code below to get the current user information. <asp:Content ContentPlaceHolderId="PlaceHolderAdditionalPageHead" runat="server"> Now copy & paste this JavaScript code after above mentioned tag : <script type="text/ecmascript">         ExecuteOrDelayUntilScriptLoaded(getUserData, "sp.js");              // To Get Current User Name     var context = null;      var web = null;      var currentUser = null;      var currentUserId=''     function getUserData() {             context = new SP.Cli
Read more

Steps to set Form based authentication (FBA) for SharePoint 2010

By -
Image
1.        DOWNLOAD FBA User Management Tool           Go to “C:\Windows\Microsoft.NET\Framework\v2.0.50727”. 2.        Open file “aspnet_regsql.exe” (with Run as Administrator).                                 I.             You will get an installation form. Hit “Next” button.                               II.             Click on “Configure SQL Server for application services” then hit “Next” button.                             III.             Now provide server name to connect & install new database for Membership. Now hit “Next” button.                             IV.             Now you get a summary page on which you will get server name & its new database name called “aspnetdb”. Click on “Next” button.                               V.             Now database has been created. Click on “Finish” button. 3.        Install FBA tool. 4.        Create a new Site collection (suppose we called it “A”). 5.        Extend this site (Suppose we called it “B”).        
Read more

SharePoint Interview Questions and Answers II

By -
What is Microsoft Windows SharePoint Services? How is it related to Microsoft Office SharePoint Server 2007? Windows SharePoint Services is the solution that enables you to create Web sites for information sharing and document collaboration. Windows SharePoint Services -- a key piece of the information worker infrastructure delivered in Microsoft Windows Server 2003ice system and other desktop applications, and it serves as a platform for application development. Office SharePoint Server 2007 builds on top of Windows SharePoint Services 3.0 to provide additional capabilities including collaboration, portal, search, enterprise content management, business process and forms, and business intelligence. What is Microsoft SharePoint Portal Server? SharePoint Portal Server is a portal server that connects people, teams, and knowledge across business processes. SharePoint Portal Server integrates information from various systems into one secure solution through
Read more

Get List Items - JavaScript

By -
  var value = SP.ListOperation.Selection.getSelectedList();   var productcollection; function getProducts(title) {     try {         var context = new SP.ClientContext.get_current();         var web = context.get_web();         var list = web.get_lists().getByTitle('product');         var query = '<View Scope=\'RecursiveAll\'>'+                         '<Query>'+                             '<Where>'+                             '<Contains>'+                                 '<FieldRef Name=\'ProductName\'/>' +                                 '<Value Type=\'Text\'>' + title +'</Value>'+                             '</Contains>'+                             '</Where>'+                         '</Query>'+                              '</View>';         var camlQuery = new SP.CamlQuery();         camlQuery.
Read more

Cross Site List Rollup Web Part for SharePoint 2010

By -
Downalod Cross Site List Rollup   Hi I was wondering that how to show a list from other site on my home page. But I didn’t get any proper & free solution. So I decided to create a Cross Site List Rollup Webpart . Now I have developed a Cross site list rollup web part which can show your list from any site to any site (within site collation only) and I am ready to share this web part with all of you.  It’s too simple in use. You have to just provide a:    Site URL (Site URL from where you want to display list or library).    List Title (Display Name of List) (Title of the list which has to be on given site URL).    View Name (Name of view which you have created to show in this web part). Now it is ready to use. Enjoy!!! Downalod Cross Site List Rollup Gaurav Goyal
Read more

Hide Recently Modified Items

By -
Image
Hide Recently Modified Items In SharePoint 2010 we all are familiar with the following annoying quicklaunch menu:   Follow these steps :  Go to  " Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Template\DocumentTemplates" . Open "wkpstd.aspx" file . Search for tag " SharePoint:RecentChangesMenu". Add visible="false"  at the end of this tag. Save this file & refresh SharePoint site .
Read more

Change Language for current user with JSOM in SharePoint Online

By -
Hi, Here is code which can change language for current user with JSOM : <script type="text/javascript" charset="utf8" src="/sites/BizDev/Style%20Library/SPS/jquery-1.11.3.min.js"></script> <select id='LangSelect' onchange="SetUserLanguage(this.value);"></select> <script type="text/javascript"> function SetUserLanguage(lCode) {     var call = $.ajax({             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/Web/Lists/GetByTitle('Languages')/items?" + "$select=Title,Code,LCode,ID&$top=5000&$filter=Code eq "+lCode,             type: "GET",             dataType: "json",             headers: {                 Accept: "application/json;odata=verbose"             }         });         call.done(function (data, textStatus, jqXHR) {             var multipleValues = [];             for (var i=0;i<data.d.results.length;i++){            
Read more

SharePoint 2010 CSS Chart

By -
Image
CSS Comments Screenshots body #s4-ribboncont{background-image:none;} /*Globally turning off bordersThis is needed to have any of the style below to take effect.   .ms-cui-groupSeparator {border-right:1px solid #E7E7E8 !important; } .ms-cui-topBar2{border-width:0px;}*/ /*Turn on separators in the Ribbon   .ms-cui-ribbonTopBars{ background: red url() repeat-x !important; padding-top:0px; } /* Set the background for the tabs at the top (Browse / Page) */ .ms-cui-cg-i{ background-image:url() !important; background-position:bottom left; background-repeat:no-repeat !important; height:15px; margin-bottom:2px; } .ms-cui-cg-db .ms-cui-cg-t{height:17px; background-image:url(‘http://integration.echn.ca/Media/SiteImages/bgBackground.gif’) !important; background-position:bottom right !important; background-repeat:no-repeat !important; margin-left:4px;} .ms-cui-cg-t-i{ color:white; font-weight:bold; } .ribbon-wrapper{position:relative;} /* Set the background for the addi
Read more