Solved

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

Posted on 2011-09-08
3
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

756 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