Leading zeros removed after a replace of non-numeric characters leaving only numeric charaters

[PriorityOne]

I need to remove the forward slashes and leave the results in a data set like the below. I keep losing the leading zeros when I do a replace all with / in the find and left the replace with blank even when the cells are formatted to Text. I am working in Excel 2010 with a regular .xlsx file - I know the csv tricks and what is necessary when I have pure numeric characters. the only thing that has worked is to put an apostrophe in front of the first characters in the first cell ('0019A in the example below) and then use the format painter to copy the format down to the other characters prior to doing the replace. I thought it might because the removal of the forward slash would make the characters numeric, lost it's text properties and removed the leading zeros, but the results are still in a cells formatted as Text. I found a formula that worked =IF(RIGHT(A1,1)="/",LEFT(A1,4),A1) which would maintain the leading zero if I did a copy paste special values into a general formatted cell. Can anyone explain why I can't use the replace function and why the formula would work with leading zeros in a general formatted cell?

0019A
0043/
0051/
0104/
0119/
0133/
0140/
0146/
0153/
0162A

 

[iliace]

When you remove the slash, Excel recognizes that your cell value is all digits and automatically assumes that you want it to be a number.

When you use the LEFT formula, it returns a text value, and if you copy/paste values, it preserves that text format. However, you will probably get an error indicator warning you that you have a "Number stored as text".

 

[PriorityOne]

Thank you. I was thinking that and yes, I have a warning about the number stored as text. So it makes a lot of sense now that the apostrophe would solve the issue which is what I thought. Is the apostrophe the only work around from a formatting standpoint?

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

a​

[/TD]
[TD]

b​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]0019a[/TD]
[TD]0019a[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]0043/[/TD]
[TD]0043[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]0051/[/TD]
[TD]0051[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]0104/[/TD]
[TD]0104[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]0119/[/TD]
[TD]0119[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]0133/[/TD]
[TD]0133[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]0140/[/TD]
[TD]0140[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]0146/[/TD]
[TD]0146[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]0153/[/TD]
[TD]0153[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]0162a[/TD]
[TD]0162a[/TD]
[/TR]
</tbody>[/TABLE]

b1=SUBSTITUTE(A1,"/","")&"" copy down

 

[iliace]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

a​

[/TD]
[TD]

b​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]0019a[/TD]
[TD]0019a[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]0043/[/TD]
[TD]0043[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]0051/[/TD]
[TD]0051[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]0104/[/TD]
[TD]0104[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]0119/[/TD]
[TD]0119[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]0133/[/TD]
[TD]0133[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]0140/[/TD]
[TD]0140[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]0146/[/TD]
[TD]0146[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]0153/[/TD]
[TD]0153[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]0162a[/TD]
[TD]0162a[/TD]
[/TR]
</tbody>[/TABLE]

b1=SUBSTITUTE(A1,"/","")&"" copy down

This approach, or the one you used with LEFT/RIGHT combination, will produce a text value. If you copy/paste values, Excel will store it as text. You could also use a TEXT function on a set of data where it is already a number (e.g. =TEXT(A1, "0000") to leftpad with zeroes, and then copy/paste values. However, I personally find the apostrophe method more reliable, for my own peace of mind if nothing else.

 

[PriorityOne]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

a​

[/TD]
[TD]

b​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]0019a[/TD]
[TD]0019a[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]0043/[/TD]
[TD]0043[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]0051/[/TD]
[TD]0051[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]0104/[/TD]
[TD]0104[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]0119/[/TD]
[TD]0119[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]0133/[/TD]
[TD]0133[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]0140/[/TD]
[TD]0140[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]0146/[/TD]
[TD]0146[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]0153/[/TD]
[TD]0153[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]0162a[/TD]
[TD]0162a[/TD]
[/TR]
</tbody>[/TABLE]

b1=SUBSTITUTE(A1,"/","")&"" copy down

I didn't realize I had more replies until I cleaned out my buried inbox. I apologize. This is a great solution and I appreciate the reply both MARZIOTULLIO and iliace !!!

 

[Peter_SSs]

If I have understood correctly, there's another pretty easy way.

With the data as posted in post #1,
- Select the column
- Data ribbon tab - Text to Columns - Delimited - Next - Clear all check boxes except 'Other' and put a / in the 'Other:' box - Next - Select 'Text' as the column data format - Finish