Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing 2-3 characters at the end
I need to remove 1 or 2 digits at the end of my cell. I did a search
and found the following post from 2003. Don't quite understand all of it. http://groups.google.com/group/micro...14ea43bc0279d4 Here is my example related to my previous post which removed the decimals but some of the cells have letters at the end and some are too long. There are 2 different spreadsheets: 1) is the previous spreadsheet in other post using the =SUBSTITUTE. I can remove the decimals. Is there a way to do the =SUBSTITUTE and remove the last 2 or 3 characters at the same time? 7.2.9.3.a.b need to have 729 2) the other spreadsheet. I just need to remove the last 2 or 3 on the end. already has 729ab sometimes 729abc need to have just 729 Thanks, T |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing 2-3 characters at the end
On Jun 8, 3:43*pm, Ty wrote:
I need to remove 1 or 2 digits at the end of my cell. *I did a search and found the following post from 2003. *Don't quite understand all of it. http://groups.google.com/group/micro...orksheet.funct... Here is my example related to my previous post which removed the decimals but some of the cells have letters at the end and some are too long. There are 2 different spreadsheets: 1) is the previous spreadsheet in other post using the =SUBSTITUTE. *I can remove the decimals. *Is there a way to do the =SUBSTITUTE and remove the last 2 or 3 characters at the same time? 7.2.9.3.a.b need to have 729 2) the other spreadsheet. *I just need to remove the last 2 or 3 on the end. already has 729ab sometimes 729abc need to have just 729 Thanks, T Adding more info. On spreadsheet #2. There are actually some letters in front of the numbers. acct729ab need to have just 729 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing 2-3 characters at the end
Give this formula a try...
=LOOKUP(9E+99,--LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8 ,9},A1&"0123456789")),LEN(A1)),".",""),ROW(INDIREC T("1:99")))) Rick Rothstein (MVP - Excel) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing 2-3 characters at the end
On Jun 8, 4:28*pm, "Rick Rothstein"
wrote: Give this formula a try... =LOOKUP(9E+99,--LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8 ,9},A1&"0*123456789")),LEN(A1)),".",""),ROW(INDIRE CT("1:99")))) Rick Rothstein (MVP - Excel) Thank you! It works. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing 2-3 characters at the end
On Wed, 8 Jun 2011 14:33:54 -0700 (PDT), Ty wrote:
On Jun 8, 4:28*pm, "Rick Rothstein" wrote: Give this formula a try... =LOOKUP(9E+99,--LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8 ,9},A1&"0*123456789")),LEN(A1)),".",""),ROW(INDIRE CT("1:99")))) Rick Rothstein (MVP - Excel) Thank you! It works. One of your examples: ----------------------------- 7.2.9.3.a.b need to have 729 --------------------------- Rick's formula returns all the digits, so it would return 7293. Is your example incorrect with a typo? Or do you really want only the first three digits? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing 2-3 characters at the end
On Jun 8, 7:32*pm, Ron Rosenfeld wrote:
On Wed, 8 Jun 2011 14:33:54 -0700 (PDT), Ty wrote: On Jun 8, 4:28*pm, "Rick Rothstein" wrote: Give this formula a try... =LOOKUP(9E+99,--LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8 ,9},A1&"0**123456789")),LEN(A1)),".",""),ROW(INDIR ECT("1:99")))) Rick Rothstein (MVP - Excel) Thank you! It works. One of your examples: ----------------------------- 7.2.9.3.a.b need to have 729 --------------------------- Rick's formula returns all the digits, so it would return 7293. Is your example incorrect with a typo? *Or do you really want only the first three digits? Yes. I only need the first 3. My way is complicated and is using a bunch of MID's and several columns and CONCATENATION's. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing 2-3 characters at the end
Rick's formula returns all the digits, so it would return 7293.
Is your example incorrect with a typo? Or do you really want only the first three digits? Yes. I only need the first 3. My way is complicated and is using a bunch of MID's and several columns and CONCATENATION's. In that case, I guess you want this modification to the formula I posted earlier... =LEFT(LOOKUP(9E+99,--LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8 ,9},A1&"0123456789")),LEN(A1)),".",""),ROW(INDIREC T("1:99")))),3) Rick Rothstein (MVP - Excel) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing 2-3 characters at the end
On Fri, 10 Jun 2011 15:43:34 -0700 (PDT), Ty wrote:
Yes. I only need the first 3. My way is complicated and is using a bunch of MID's and several columns and CONCATENATION's. I see Rick has already posted a modification to his formula to limit the return to only three digits. So long as you have covered all the variations in data in and fully specified what you want out, that formula will work just fine. If your digits might be separated by characters other than periods (.), then you need a different solution. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing first 10 characters of text | Excel Worksheet Functions | |||
removing characters | Excel Discussion (Misc queries) | |||
Removing characters | Excel Discussion (Misc queries) | |||
Removing characters | Excel Discussion (Misc queries) | |||
Removing characters | Excel Worksheet Functions |