Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-12-2020, 05:02 PM
cefd1 cefd1 is offline remove character in a cell Windows 10 remove character in a cell Office 2019
Novice
remove character in a cell
 
Join Date: Jul 2019
Posts: 10
cefd1 is on a distinguished road
Default remove character in a cell

I have a worksheet that has data listed examples: 9-2 - Washington and 9-2-30 - Washington.

Is there a code that removes the last hyphen between the numbers and beginning of letters that I could drag down my column for a large worksheet?



Thanks
Reply With Quote
  #2  
Old 03-12-2020, 08:59 PM
Marcia's Avatar
Marcia Marcia is offline remove character in a cell Windows 7 32bit remove character in a cell Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 257
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

What I do is to select all the cells that need editing>Ctrl F>type '-' in the Find What box>click on the Replace With>Replace All
The single quote in the '-' is for clarity only.
Reply With Quote
  #3  
Old 03-13-2020, 12:31 AM
macropod's Avatar
macropod macropod is offline remove character in a cell Windows 7 64bit remove character in a cell Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,038
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

Quote:
Originally Posted by Marcia View Post
What I do is to select all the cells that need editing>Ctrl F>type '-' in the Find What box>click on the Replace With>Replace All
That, however, would remove all the hyphens...

Judging by the posted example, one needs to Find ' - ' and replace with ' '. Or, as per the OP's request, a formula could be used:
=SUBSTITUTE(A1, " - "," ")
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #4  
Old 03-13-2020, 02:14 AM
ArviLaanemets ArviLaanemets is offline remove character in a cell Windows 8 remove character in a cell Office 2016
Expert
 
Join Date: May 2017
Posts: 545
ArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the rough
Default

Replace "- " with "". Replace All. (between numbers, hyphens are followed with number immediately, so they aren't replaced)
Reply With Quote
  #5  
Old 03-13-2020, 05:16 AM
cefd1 cefd1 is offline remove character in a cell Windows 10 remove character in a cell Office 2019
Novice
remove character in a cell
 
Join Date: Jul 2019
Posts: 10
cefd1 is on a distinguished road
Default

Thanks, got it working
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
remove character in a cell Formula Max Character per cell + spread over other colums lily Excel 4 03-29-2015 07:08 PM
remove character in a cell I want Marco solution to remove specific Raw having only .(dot Character) PRA007 Word VBA 2 12-04-2014 01:53 AM
remove character in a cell How do I output special character to a specific cell (x,y)? norwood Word VBA 2 01-31-2014 01:26 PM
Display a label character in a data cell markg2 Excel 9 09-07-2013 05:56 AM
Remove first character from text file dwillyfan Word VBA 1 09-04-2012 08:29 PM


All times are GMT -7. The time now is 09:47 AM.


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