Solved

capture values in calculated column

Posted on 2013-02-05
7
244 Views
Last Modified: 2013-02-06
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
Comment
Question by:Isaac
  • 4
  • 3
7 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
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

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


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

Author Comment

by:Isaac
ID: 38857491
Actually, what I did was wrong.

Yours is right!

I will try and get the 3rd and 4th.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 44

Accepted Solution

by:
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)))

Open in new window


The above four columns should now have the correct values.

HTH
Rainer
0
 
LVL 5

Author Comment

by:Isaac
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
metric value 1
Here's how i sort
sort
Any ideas of why it's not sorting?

doesn't make sense....
0
 
LVL 5

Author Comment

by:Isaac
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

by:Rainer Jeschor
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently came across an issue with a MOSS 2007 deployment where access into some sub-sites were denied, even for the MOSS farm administrators. A bit of background to the setup of this MOSS farm; this was a three server setup, consisting of a fr…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question