Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delete all words in cell more than 5 words 7 48
Generating a graph via Excel 3 28
Search for a value in Column? 5 22
Most Consistent Performer 4 22
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now