capture values in calculated column

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.


Correct (Needs to be sorted like this)

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?
IsaacSharePoint Client Side DeveloperAsked:
Who is Participating?
Rainer JeschorConnect With a Mentor Commented:
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.

Rainer JeschorCommented:

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.

IsaacSharePoint Client Side DeveloperAuthor Commented:
Here's how I got the 1st number:

=if(find(".",[metric id])=2,left(1),left(2))
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

IsaacSharePoint Client Side DeveloperAuthor Commented:
Actually, what I did was wrong.

Yours is right!

I will try and get the 3rd and 4th.
IsaacSharePoint Client Side DeveloperAuthor Commented:
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                             10                             16                             17                             2

Below is an image of what I created
metric value 1
Here's how i sort
Any ideas of why it's not sorting?

doesn't make sense....
IsaacSharePoint Client Side DeveloperAuthor Commented:
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.111 => 1.211 => 10.111 => 16.185 => 17.291 => 2.111 => 1.131 => 1.115
Rainer JeschorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.