How to Separate or Remove digits from a string of Numbers ?

General discussion on MS Excel and its spreadsheets.

How to Separate or Remove digits from a string of Numbers ?

Postby Brian L. Chacon » Tue Jul 29, 2014 7:21 am

Hello,

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.

Thanks in Advance,
Brian L. Chacon
 
Posts: 20
Joined: Mon Mar 24, 2014 6:23 am

Re: How to Separate or Remove digits from a string of Number

Postby Gregory A. Chase » Tue Jul 29, 2014 11:05 am

Hello,

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

Thanks
Gregory A. Chase
 
Posts: 22
Joined: Mon Mar 24, 2014 6:14 am


Return to MS Excel- Help Forum

Who is online

Users browsing this forum: No registered users and 1 guest

cron