Solved

# capture values in calculated column

Posted on 2013-02-05
240 Views
I have a column that called "Metric ID" and it contains data as you see below.  The problem I'm having comes when I sort.  It sorts incorrectly because it's a single line of text.

Wrong
1.1.1.1
1.2.1.1
10.1.1.1
16.1.8.5
17.2.9.1
2.1.1.1

Correct (Needs to be sorted like this)
1.1.1.1
1.2.1.1
2.1.1.1
10.1.1.1
16.1.8.5
17.2.9.1

Any ideas?

I have an idea with calculated columns but not sure how to do it.
I will create 4 calculated columns.
calculatedColumn1 = first value before the period
calculatedColumn2 = 2nd value before the period
calculatedColumn3 = 3rd value before the period
calculatedColumn4 = 4th value after the period

How do I capture each value using a calculated column?
0
Question by:Isaac
• 4
• 3

LVL 44

Expert Comment

ID: 38857460
Hi,

the first two columns can be calculated like this:
First part:
=LEFT([Metric ID],(SEARCH(".",[Metric ID],1)-1))

Second part:
=LEFT(RIGHT([Metric ID],(LEN([Metric ID])-SEARCH(".",[Metric ID],1))),(SEARCH(".",RIGHT([Metric ID],(LEN([Metric ID])-SEARCH(".",[Metric ID],1))),1)-1))

and so on.

HTH
Rainer
0

LVL 5

Author Comment

ID: 38857466
Here's how I got the 1st number:

=if(find(".",[metric id])=2,left(1),left(2))
0

LVL 5

Author Comment

ID: 38857491
Actually, what I did was wrong.

Yours is right!

I will try and get the 3rd and 4th.
0

LVL 44

Accepted Solution

Rainer Jeschor earned 500 total points
ID: 38857540
Hi,
found a little bit simpler way:
``````First part
=LEFT([Metric ID],(SEARCH(".",[Metric ID],1)-1))

Second part
=LEFT(RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1))),(SEARCH(".",RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1))),1)-1))

Third part
=LEFT(RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1+LEN(SecondPart)+1))),(SEARCH(".",RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1+LEN(SecondPart)+1))),1)-1))

Last part
=RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1+LEN(SecondPart)+1+LEN(ThirdPart)+1)))
``````

The above four columns should now have the correct values.

HTH
Rainer
0

LVL 5

Author Comment

ID: 38857595
Actually, there is no need for me to get the 3rd and 4th column because I can only sort up to  columns.

Anyway, that did not work as i thought it would.  Even though I sorted the calculated column, it still sorted wrong.

See below.  "Metric Value 1" is my calculated value that extracts the first value.

Here's what happens when I sort by "Metric Value 1"....

Metric ID              Metric Value 1
1.1.1.1                               1
1.2.1.1                               1
10.1.1.1                             10
16.1.8.5                             16
17.2.9.1                             17
2.1.1.1                             2

Below is an image of what I created

Here's how i sort

Any ideas of why it's not sorting?

doesn't make sense....
0

LVL 5

Author Comment

ID: 38858000
How can I do the following?
Remove all the "." except the first one.  I can probably make that column a decimal and sort it that way.

1.1.1.1 => 1.111
1.2.1.1 => 1.211
10.1.1.1 => 10.111
16.1.8.5 => 16.185
17.2.9.1 => 17.291
2.1.1.1 => 2.111
1.1.3.1 => 1.131
1.1.1.5 => 1.115
0

LVL 44

Expert Comment

ID: 38858234
Hi,
OK - but as I have seen you have already entered another question on the sorting issue.
I think your initial question in splitting up the values is answered.

I will post my proposed solution into the other question.

Thanks and HTH
Rainer
0

## Featured Post

There is one common problem that all we SharePoint developers share: custom solution deployment. This topic can't be covered fully in this short article, so all I want to do in this one is to review it from a development-to-operations perspectiv…
Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…