EXCEL: Delete trailing zeros or reformat cell

[wolves]

Importing data into Excel, I do not have the source code where the data comes from.

The data look like this:

[highlight]
0011122588523400000000000000B1111
[/highlight]

When it's imported into Excel, and I create the line breaks,
the data looks like this.
[highlight]
$11,225,885,234.00 B 1111
[/highlight]

But it should look like this:
[highlight]
$112,258,852.34 B 1111
[/highlight]

Not sure why the number adds 2 zeros on the end. It's imported as a General field. Switching the format in Excel to Number or Currency does nothing to this.

Was thinking either doing a script(not sure how yet) to get rid of this, or finding a more permanent solution.

Any ideas,

thanks in advance.

 

[DaleWatson123321]

Hi wolves,

Here's a formula solution...

=TEXT((VALUE(MID(A1,4,11))/100),"$000,000.00")&" "&LEFT(RIGHT(A1,5),1)&" "&RIGHT(A1,4)

If your data is in Column A, you could place this in Column B and copy down for the number of rows required.

Hope this helps.

Regards, Dale Watson

 

[wolves]

Thanks, will give it a shot and see what happens.

 

[SkipVought]

W,

You're assuming that Excel will ASSUME an IMPLIED DECIMAL.

I assume that the results are in 3 columns
[tt]
A: $11,225,885,234.00
B: B
C: 1111
[/tt]
create a cell containing .01 and COPY
select column A, Edit/Paste Special - Operation Multiply

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[wolves]

Thanks for the info. I did the *.01 and divided by 10 and changed the decimal places. All works, may start looking into macro so I can run the macro and not have to type out the formula each time.

Thanks again.