Truncated Cells return incorrect values

Nomas

Board Regular
Joined
Jun 14, 2011
Messages
91
Hi there!

Question regarding a cell lookup that returns a truncated value. I am working in an excel 2003 file (needs to stay in this format for the end user). I have the following function

=IF(AY2="","blank",LEFT(AY2,9))

The main information I want is the first 9 characer in AY2 if there are values there. The AY column could have any number of characters but I always want the first 9. All had been going well when I ran into the value

<TABLE style="WIDTH: 173pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=230><COLGROUP><COL style="WIDTH: 173pt; mso-width-source: userset; mso-width-alt: 6542" width=230><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 173pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=230 align=right>433892720000082000000000</TD></TR></TBODY></TABLE>

Because of the length it truncates it to

4.3389272

This gives me the wrong number

I need it to return

433892720

Any guidance is appreciated!

Thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Updated info:

I tried to import the data as text after reading some old posts but it imports the texted in a truncated 4.333 e+11 format. Thank in advance for any help
 
Upvote 0
You could use

=IF(AY2="", "blank", LEFT(TEXT(AY2, "0"),9))

... but it would be better if these cells were formatted as text and the values converted to text, then you could go back to your original formula, and you wouldn't lose additional non-zero characters.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top