formatting cells

B

bchasse

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/com...dg=microsoft.public.excel.worksheet.functions
 
P

Peo Sjoblom

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
 
P

Pete_UK

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
 
Top