Simple me thinks

RJL_ST_G

New Member
Joined
Sep 8, 2009
Messages
14
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
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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
 
Last edited:

RJL_ST_G

New Member
Joined
Sep 8, 2009
Messages
14
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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

New Member
Joined
Sep 8, 2009
Messages
14
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
=LEFT(A1,FIND("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
Hope that’s clear enough!!<o:p></o:p>
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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"...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top