how to remove 5th character if its a zero

[eeps24]

I have a long list of accounts... example 0105010010 I need a formula that if the 5th character (left to right) is a zero, to remove it, if not zero then leave it alone. Can someone please help me?

Thank you!

[dominicb]

Good afternoon eeps24

Perhaps something like :

Please Login or Register  to view this content.

(Assumes your list starts at A1)

HTH

DominicB

[eeps24]

The cell I want to work on begins on D2 and downward. I tried to use your code and replaced A1 with D2 but it did not work. Here is my scree shot.

Capture.JPG

[Jonmo1]

It may be that the value in D2 is actually 105010010, but you have custom formatting that is adding the leading 0..
What does this return
=ISNUMBER(D2)
?

Pictures usually aren't very helpfull because we can't then copy/paste it into our own sheets for troubleshooting.
Can you post a sample book instead?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[eeps24]

I am loading a comma delimited file and yes the file does contain 0105010010. I had to format the column as text in order to keep the leading zero other wise I was going to lose it. I think I uploaded a sample to my post or atleast tried.

[Jonmo1]

Dominic's formula works for me.

The picture you posted in post #3..
E2, is that actually what happened when you entered the formula, or were you just trying to show the formula that you entered?

If that's what actually happened, then it means that E2 is formatted as TEXT
Change the format of the cell with the formula to General (or anything other than text) and RE-ENTER the formula

[AlKey]

Maybe this one

=IF(RIGHT(LEFT(D2,5))="0",REPLACE(D2,5,1,""),D2)

[eeps24]

it worked! (dominc's forumula) few questions...

1. Yes E2 was formatted as text, when I changed it to general it worked. Why wouldnt it work as text? Forumuals cant be in text mode?
2. Great formula, im glad it worked but I would love to understand the coding behind it. Can you please break it down for me exactly what that forumula is saying?

THANK YOU!

[Jonmo1]

1. Yes E2 was formatted as text, when I changed it to general it worked. Why wouldnt it work as text? Forumuals cant be in text mode?

It depends on the order of events...Which came first, the Chicken or the Egg.

If the Cell is formatted as text FIRST, and THEN a formula is entered...
Then Excel sees the formula as just a text string (because you told it to with cell formatting), and shows it exactly as it's typed.

If the Cells if formatted other than text, then a formula entered, THEN format the cell as text, it's fine.
But at this point, if you ever have to RE-Enter that formula (edit it for some reason), then it will be back to the state you had before in post #3.

However, It's almost never necessary to format a cell with formula as Text.
Because if the formula does return a true text string, then it will be as such regardless of the formatting of the cell.

Hope that helps.

[eeps24]

thank you but I was also wondering if you can break down this formula to me? Whats the MID for? and I see LEFT and RIGHT, what are those doing exactly?
=IF(MID(A1,5,1)="0",LEFT(A1,4)&RIGHT(A1,5),A1)

[dominicb]

Hi eeps24

Please Login or Register  to view this content.

It asks a question - IF the MIDdle of string in A1, 5 places in, 1 character long = 0 then show the LEFT 4 characters of string A1, and (&) the RIGHT 5 characters of A1. This effectively rebuilds the account number, leaving out the zero.

Otherwise, just show the contents of A1 as they are.

HTH

DominicB

[eeps24]

sweet! thank you... and I would use BEG and END as well for different positions?

[dominicb]

Hi

No - you'd use LEFT() and RIGHT(), thus :

Please Login or Register  to view this content.

HTH

DominicB