Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-08
3
Medium Priority
?
211 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 (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 400 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 1200 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 20

Assisted Solution

by:Alan
Alan earned 400 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

782 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