Simple me thinks

RJL_ST_G

New Member
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

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

SteveO59L

Well-known Member
=left(g21,len(g21)-2)

Lewiy

Well-known Member
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
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

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

MrExcel MVP
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"...

Replies
1
Views
74
Replies
18
Views
282
Replies
19
Views
400
Replies
7
Views
235
Replies
3
Views
67

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.

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

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