Solved

capture values in calculated column

Posted on 2013-02-05
7
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you create your solutions on SharePoint sooner or later you will come upon a request to set  permissions of the item depending on some of the item's meta-data - the author, people assigned as approvers, divisions, categories etc. The most natu…
I used to be SharePoint evangelist in our company, so my Outlook always full of questions about how to do this, or where I can find that. One day I found such an email with the following question: "how to attach 3-State workflow (one of the workflow…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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