asked on

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.

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?

Rainer Jeschor

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

Isaac

ASKER

Here's how I got the 1st number:

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

Isaac

ASKER

Actually, what I did was wrong.

Yours is right!

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

ASKER CERTIFIED SOLUTION

Rainer Jeschor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Isaac

ASKER

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....

Isaac

ASKER

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

Rainer Jeschor

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