Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-23-2014, 02:55 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Separate/ Remove digits from a string of Numbers Windows 7 32bit Separate/ Remove digits from a string of Numbers Office 2007
Advanced Beginner
Separate/ Remove digits from a string of Numbers
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 07-23-2014, 04:12 AM
macropod's Avatar
macropod macropod is offline Separate/ Remove digits from a string of Numbers Windows 7 32bit Separate/ Remove digits from a string of Numbers Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 07-23-2014, 04:51 AM
QA_Compliance_Advisor QA_Compliance_Advisor is offline Separate/ Remove digits from a string of Numbers Windows 7 32bit Separate/ Remove digits from a string of Numbers Office 2007
Advanced Beginner
Separate/ Remove digits from a string of Numbers
 
Join Date: Jul 2014
Posts: 44
QA_Compliance_Advisor is on a distinguished road
Default

That is awesome worked like a treat - the only thing I didn't think of was "iserror" and "Find" function.

Thanks so much
Reply With Quote
  #4  
Old 07-23-2014, 04:59 AM
macropod's Avatar
macropod macropod is offline Separate/ Remove digits from a string of Numbers Windows 7 32bit Separate/ Remove digits from a string of Numbers Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Tags
left & right function, remove digits, separate

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separate/ Remove digits from a string of Numbers 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
Separate/ Remove digits from a string of Numbers Problems merging in last 4 digits of an account higher than 16 digits Glynda Mail Merge 1 04-08-2011 12:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft