#1
|
|||
|
|||
Separate/ Remove digits from a string of Numbers
I have over 10,000 entries of part numbers with variable lengths for example
(1) 000 (2)00.00 (3)00.00.00 the rule I need is if the cell has a "." need to remove 2 from the right and if it has no "." need to remove 1. Can anyone think of a formula that could do this, I have tried Left & Right Function but it only works to a certain degree. |
#2
|
||||
|
||||
For the data you posted, and depending on whether you want to keep the final period, either:
=IF(ISERROR(FIND(".",A1)), LEFT(A1,LEN(A1)-1), LEFT(A1,LEN(A1)-2)) or: =IF(ISERROR(FIND(".",A1)), LEFT(A1,LEN(A1)-1), LEFT(A1,LEN(A1)-3))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
That is awesome worked like a treat - the only thing I didn't think of was "iserror" and "Find" function.
Thanks so much |
#4
|
||||
|
||||
Even simpler is:
=LEFT(A1,LEN(A1)-2+ISERROR(FIND(".",A1))) or: =LEFT(A1,LEN(A1)-3+2*ISERROR(FIND(".",A1)))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
left & right function, remove digits, separate |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Display Numbers in CSV file over 15 digits | linan123 | Excel | 1 | 06-24-2014 01:54 PM |
How to extract only numbers from a STRING? | Learner7 | Excel | 3 | 07-02-2013 06:25 AM |
Separate heading numbers | darksupernova | Word | 16 | 03-02-2013 06:09 AM |
Separate the digits into 3 combinations | Jasa P | Word VBA | 1 | 08-19-2012 11:04 PM |
Problems merging in last 4 digits of an account higher than 16 digits | Glynda | Mail Merge | 1 | 04-08-2011 12:17 AM |