Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-11-2021, 12:30 PM
Haha88 Haha88 is offline Replace # with ' from original data Windows 8 Replace # with ' from original data Office 2010 32bit
Advanced Beginner
Replace # with ' from original data
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default Replace # with ' from original data

Hi,



I'm seeking for some help with formula to take out the # from order column and replace with '. I have to keep the order column as is.

ORDER Result
#000009439 ='000009439
#CLR000387 CLR000387
#2000000692 ='2000000692

Many thanks,
Haha88
Attached Files
File Type: xlsx Fomular help.xlsx (9.5 KB, 10 views)
Reply With Quote
  #2  
Old 03-11-2021, 10:05 PM
Purfleet Purfleet is offline Replace # with ' from original data Windows 10 Replace # with ' from original data Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Try =IF(ISNUMBER(MID(C3,2,1)+0)=TRUE,SUBSTITUTE(C3,"#" ,"'"),SUBSTITUTE(C3,"#",""))
Attached Files
File Type: xlsx Copy of Fomular help_purfleet.xlsx (10.1 KB, 5 views)
Reply With Quote
  #3  
Old 03-12-2021, 01:46 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Replace # with ' from original data Windows 7 64bit Replace # with ' from original data Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

If your purpose is to make text out of a number without loosing the leading zeroes try
Code:
=RIGHT(TEXT(C3;"0");LEN(C3)-1)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Replace # with ' from original data newb - search and replace - WILDCARDS - keep original...? Bansaw Word 4 09-01-2016 11:16 AM
Replace contents form 1 workbook with another based on simular data shabbaranks Excel 1 12-05-2012 11:11 PM
Hyperlink/Data Insert & replace jclinton Word 1 09-19-2012 07:22 PM
Replace # with ' from original data data change how to convert to original gsrikanth Excel 6 12-30-2011 08:01 AM
How to restore original data format - from a .pdf file to Excel columns KIM SOLIS Excel 1 09-18-2011 10:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:49 PM.


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