If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
formatting cells
I am working with a very large dataset and need to know if there is a way to
remove a single digit from the middle of a string of digits. For example, if I have a list of the following numbers and want to remove the "0" from the middle in each cell, is there a way to format the row so that the "0" will be removed from each cell? 1468055 1567023 6445065 5432014 5465014 The "0" that I want to remove is in the same location in each cell. Is there a way to format each cell so that I can remove the "0"? Thanks for all the help! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#2
|
|||
|
|||
formatting cells
You can use a help column like
=--SUBSTITUTE(A1,MID(A1,5,1),"") if the 5th character always is a zero, if not always a zero and those that are not zeros shouldn't be changed =IF(MID(A1,5,1)="0",--SUBSTITUTE(A1,MID(A1,5,1),""),A1) -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "bchasse" wrote in message ... I am working with a very large dataset and need to know if there is a way to remove a single digit from the middle of a string of digits. For example, if I have a list of the following numbers and want to remove the "0" from the middle in each cell, is there a way to format the row so that the "0" will be removed from each cell? 1468055 1567023 6445065 5432014 5465014 The "0" that I want to remove is in the same location in each cell. Is there a way to format each cell so that I can remove the "0"? Thanks for all the help! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#3
|
|||
|
|||
formatting cells
I am still having trouble with this. It seems the formula is taking out the
wrong number. In the previous email I used a sample list, but here are a few numbers from the actual dataset I am working with: 1010111110 1010121110 1010131110 1470121100 2000111100 1710111100 1710111200 1640111100 1240111121 1240111122 1240121111 1750111110 2030111100 1320111110 I want to take out the zero fourth from the left, but don't want to take any other numbers out. So, for example, the first number would become 101111110, the second would be 101121110, etc. How can I make this formula work for me? Thanks in advance for all your help! I really appreciate it! "Peo Sjoblom" wrote: You can use a help column like =--SUBSTITUTE(A1,MID(A1,5,1),"") if the 5th character always is a zero, if not always a zero and those that are not zeros shouldn't be changed =IF(MID(A1,5,1)="0",--SUBSTITUTE(A1,MID(A1,5,1),""),A1) -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "bchasse" wrote in message ... I am working with a very large dataset and need to know if there is a way to remove a single digit from the middle of a string of digits. For example, if I have a list of the following numbers and want to remove the "0" from the middle in each cell, is there a way to format the row so that the "0" will be removed from each cell? 1468055 1567023 6445065 5432014 5465014 The "0" that I want to remove is in the same location in each cell. Is there a way to format each cell so that I can remove the "0"? Thanks for all the help! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
#4
|
|||
|
|||
formatting cells
Try this:
=VALUE(LEFT(A1,3)&RIGHT(A1,LEN(A1)-4)) assuming your data is in column A. Copy the formula down by double-clicking the fill handle (the small black square in the bottom right cormer of the cursor). Hope this helps. Pete |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating links to cells with no content, but that have formatting | cerah | General Discussion | 1 | January 24th, 2006 11:31 PM |
Conditional formatting...cont. from 9/25 | Guenzak | General Discussion | 4 | September 26th, 2005 10:55 PM |
Formatting Cells | Scott | General Discussion | 3 | August 26th, 2005 07:34 PM |
can cells apply conditional formatting using the internal clock? | Dan Kuzmanovic | Charts and Charting | 1 | April 23rd, 2004 05:54 PM |
Formatting Cells in Excel 2003 | Bernie | Worksheet Functions | 1 | December 18th, 2003 02:42 PM |