Simple me thinks

[RJL_ST_G]

Hi all,

The problem i require some assitance with is this.

I have a column of numerical identifiers.
Each identifier has 6, 7 or 8 digits, which are then followed by three more numbers puncuated with decimal points i.e.

100001.1.1.1
100001.1.2.1
100001.1.2.2
1000002.1.1.2
1000002.1.1.3
1000002.1.2.1
etc...

I am looking for a way of removing the last 'decimal point' and number from each identifier. I do not mean deleting and replacing original, I only want to create a copy in a following column without the additional number.

Currently i am using the process of Text to Columns,Delimited,Other -Decimal Point. Then using the '&' function in a formula to reconnect the first two 'decimal points' and their adjacent numbers back to the first string of adjacent numbers.

The end product i require from the list of numbers above is as follows

100001.1.1
100001.1.2
100001.1.2
1000002.1.1
1000002.1.1
1000002.1.2

If anyone can help me with this, I would be most obliged.

regards,

Rob

 

[SteveO59L]

=left(g21,len(g21)-2)

 

[Lewiy]

Try this formula:

=LEFT(A1,FIND("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1)

This will work regardless of how many digits appear after the last decimal

 

[RJL_ST_G]

Hi,

Thanks both very much for your help.

Steve that works perfectly but as Lewiy seemed to predict i do require a formula which will remove the digits after the last decimal regardless of how many there are.

Lewiy can you perhaps explain the significance of the @ and the function of the SUBSTITUTE and LEN so as i can understand the formula better.

thanks again!

 

[Jonmo1]

The difference between the two is not really HOW MANY decimal points there are.

Both will remove the last decimal...

The difference is Steve's strictly just removes the last 2 characters. Whatever they are. So it assumes your values will ALWAYS have the decimal as the next to last character. And the numbers following the decimal are ALWAYS only 1 character...
Not like 100001.1.1.12
In that case, Steve's would return
100001.1.1.

Lewiy's formula literally searchs for the LAST decimal point. Steve's does not.

Depending on your situation, either would be fine.
If the last number after the last decimal is ALWAYS only 1 digit, Steves' is fine.
If the last number might be 2 or more digits, use Lewiy's.

 

[RJL_ST_G]

Thanks Jonmo,

That is what i understood to be correct.

I just wished to have more detail on the Subsitute and Len and also the significance of the @ in the formula.

thanks

 

[Lewiy]

=LEFT(A1,FIND("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Section 1: LEN(A1)-LEN(SUBSTITUTE(A1,".","")) will basically work out how many decimal points there are in the text as it takes the total length of the string and take away the length of the string when all the decimal points have been removed.<o></o>
<o> </o>
Section 2: SUBSTITUTE(A1,".","@",Section 1) replaces the last decimal point with an “@” sign (this can be any character as long as it does not appear in the original string so that it is unique. After this an original string of 11.2.3 will become 11.2@3.<o></o>
<o> </o>
Section 3: FIND("@",Section 2) finds the position of the @ sign in section 2 (hence the position of the last decimal point in the original string).<o></o>
<o> </o>
Section 4: LEFT(A1,(Section 3) -1) This takes the number from section 3 and takes away 1 from it which gives you the position of the last character that you want, then simply plugging this into the LEFT function will give you the original string up to the position just before the last decimal point.<o></o>
<o> </o>
Hope that’s clear enough!!<o></o>

 

[Jonmo1]

Hi,

Thanks both very much for your help.

Steve that works perfectly but as Lewiy seemed to predict i do require a formula which will remove the digits after the last decimal regardless of how many there are.

Lewiy can you perhaps explain the significance of the @ and the function of the SUBSTITUTE and LEN so as i can understand the formula better.

thanks again!

Sorry, thought you meant "Regardless of how many decimal points there are"...