Using Calculated Columns to do a split string.


Using Calculated Columns to do a split string.

E.g. Your Main Column (ColumnMain) has value 72;#Joe Bloggs

To separate the string into 2 parts using # as the delimiter in this case; you can use any character as a delimiter.

We create 2 calculated columns: Column 1 & Column2

  • The calculation for Column 1 would be

=LEFT(ColumnMain,(SEARCH(“;#”,ColumnMain,1)-1))

& would return 72

  • The calculation for Column 2 would be

=RIGHT(ColumnMain,(LEN(ColumnMain)-SEARCH(“#”,ColumnMain,1)))

& would return Joe Bloggs

Any queries drop me an email.

Thanks.

Advertisements

3 Responses to Using Calculated Columns to do a split string.

  1. Vamsi says:

    in my case, it is a,b,c. How do i get the value b out??
    for “a” i ll use LEFT(…) and for “c” i ll use RIGHT(….)
    wat about “b”??

  2. shravan says:

    It worked thanks a lot it saved my hours of work

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: