Need to replace ' and keep leading zeors for data that has leading zeros in a large set of data.

[sridevi.vishnuvajjala]

Hi I have a file (large file) that I import from Datewarehouse server. We have used " to keep the leading zeros as it its getting truncated when improting to excel file from the server. Now when file is opened I see ' for all the numbers even though they do not have leading zeros as it converted the numbers to text. I need to copy the data to some other files and upload to another audit tool. My challange is that I want to remove the apostphe for all the data but keep leading zeros for the ones that have. Is there any formula or any way to do it. Attaching sample file with two tab first one with data that is currently seen and the second one as desired output. I really thank and appreciate any help.

 

[vletm]

sridevi.vishnuvajjala​

Do output need to be number or text?
if text then check C-column
if number then You could use format to see it as You need too.

 

[sridevi.vishnuvajjala]

The problem is that the length of MMD may vary. In that case how do I format

 

[vletm]

sridevi.vishnuvajjala​

... the problem?
Could You answer my 1st question?
... Do output need to be number?
... Do output need to be text?

Could You send a sample file which shows that the length of MMD may vary?
... and after that ... Your expected results.

Compare my sent files cell C2 and E2
... both looks same, but C2 is text and E2 is number.
I need to copy the data to some other files and upload to another audit tool
Do Your used ... another audit tool ... use data as number or text.

Some bonus questions:
Your: I import from Datewarehouse server
Why do You import those apostphes?
Could Your import that data without those apostphes?
... or why those are there?
For some reason someone has added those and
now, You've a task to remove those ... isn't that double work?

You also wrote that Your file is ... large
... then that would mean by Your idea ... many formulas.
Do You need to do something like this once a year or more often?

 

[Peter Bartholomew]

The problem is that the length of MMD may vary. In that case how do I format

@vletm's question is important. Although they look similar superficially the internal representation of a number and that of the ASCII string are very different (even though the characters may include numerical digits). You need to know what the audit tool expects since it will receive data based upon the underlying value held, not the format as displayed.

 

[Kamisato Ayaka]

The problem is that the length of MMD may vary. In that case how do I format

you can use =Substitute(Text(B2,0),"'",)