Solved

Find and replace a letter to 0 in front of exisiting numbers

Posted on 2011-09-08
3
204 Views
Last Modified: 2012-05-12
Hello,

I have over 10000 rows and in one column have values (ex. S012224). I want to replace the S to a zero but when I use the find and replace method the leading zeros disappear. All the fields are formatted as text yet excel thinks its a number after I execute the replacement. I also want to avoid using '0 in the replace with field. How do I change this so that the leading zeros do not disappear?

Thanks for your help!
0
Comment
Question by:Jintonix415
3 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 100 total points
ID: 36506342
Hello,

Instead of find and replace, you could use a helper column with this formula

=SUBSTITUTE(A1,"S","0")&""

Then copy the helper column and use Paste Special - Values to paste over the original data. Delete the helper column.

cheers, teylyn
0
 
LVL 7

Accepted Solution

by:
andymacf earned 300 total points
ID: 36506351
In the 'Format Cells' area, select 'Custom' under the number tab and in the 'Type' field type the number of zeros depending on how many you digits you want to display and this will populate the field with the same amount of digits each time

Hope this helps
Andy
0
 
LVL 12

Assisted Solution

by:Alan3285
Alan3285 earned 100 total points
ID: 36506360
Hi,

If your list is in A1:A10000 then you could enter the following in B1:

=SUBSTITUTE(A1,"S","0")

and copy down.

If you want to replace the first character (whether it is an "S" or anything else) then use this and copy down:

="0"&MID(A1,2,999)

the second would also work in the more specific case of course.

HTH,

Alan.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question