Solved

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

Posted on 2011-09-08
3
200 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dynamic Chart Range 13 36
Excel Formula 16 45
Manipulate Range in Excel VBA 6 24
If help 9 48
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

773 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