Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-27-2013, 05:24 PM
jyfuller jyfuller is offline Need an excel formula to remove dashes in a number sequence Windows 7 64bit Need an excel formula to remove dashes in a number sequence Office 2007
Advanced Beginner
Need an excel formula to remove dashes in a number sequence
 
Join Date: Jun 2012
Posts: 32
jyfuller is on a distinguished road
Default Need an excel formula to remove dashes in a number sequence

Hi,

I have attached a file. I need an excel formula in excel that:

1. Separates the last eight numbers from the cells A1, A2, and A3, AND removes the dash. I only want the last eight numbers from each sequence to appear in the cell (without dashes) because I need to input the 8 numbers into a system. I have given an example of how it should appear in the excel file.



Additionally, if there is a macro that would be able to go in the cell and delete the dash in each cell so that only the 8 last numbers remain, this would be useful for another file I have and also to learn how to code the Macro correctly. I want a Macro that would remove the dashes from an entire column of number sequences and bring back the 8 digits (not sure if thats possible). Let me know.

Thank you.
Attached Files
File Type: xlsx separate.xlsx (8.2 KB, 10 views)
Reply With Quote
  #2  
Old 02-27-2013, 06:03 PM
macropod's Avatar
macropod macropod is offline Need an excel formula to remove dashes in a number sequence Windows 7 64bit Need an excel formula to remove dashes in a number sequence 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

Try:
=LEFT(SUBSTITUTE(A1,"-",""),8)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-27-2013, 08:30 PM
jyfuller jyfuller is offline Need an excel formula to remove dashes in a number sequence Windows 7 64bit Need an excel formula to remove dashes in a number sequence Office 2007
Advanced Beginner
Need an excel formula to remove dashes in a number sequence
 
Join Date: Jun 2012
Posts: 32
jyfuller is on a distinguished road
Default

Thanks a bunch. Thats a genius formula. Substitution is a great formula to know. Thank you. .
Reply With Quote
  #4  
Old 02-27-2013, 10:32 PM
macropod's Avatar
macropod macropod is offline Need an excel formula to remove dashes in a number sequence Windows 7 64bit Need an excel formula to remove dashes in a number sequence 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

In your initial post, you referred to a macro. Now that you have the formula, is a macro required?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need an excel formula to remove dashes in a number sequence Formula to add number of pallets shipped piglovindillard Excel 2 08-13-2012 06:40 AM
letters are dashes pmd Word 5 03-05-2012 08:08 PM
number remove, data get gsrikanth Excel 5 02-23-2012 05:42 AM
Why word does not wrap on dashes? lyrebird Word 3 06-23-2010 01:32 AM
Need an excel formula to remove dashes in a number sequence Excel sheet Sequence Number belloffice Excel 1 02-25-2010 02:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:58 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