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

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

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

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