Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deferring conditional formatting? | Excel Discussion (Misc queries) | |||
Formatting a group of cells for text | Excel Discussion (Misc queries) | |||
Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
Excel should allow more options for formatting cells | Excel Worksheet Functions | |||
How to copy "edge" formatting to other cells. | Excel Discussion (Misc queries) |