LEN vs Characteristic count

[R2ah1ze1l]

I have a file that holds some data, I wanted to keep the characteristics in specified cells to less then a length of 18 characters total.
I tried using the round function, then the trunc function. When I use the len function it returns a value of 13. All things should be good right?
I seem to be getting an error on a sample of lines where it appears there is still greater than 18 characters. Does trunc only reduce the value on the surface?

FYI I performed the trunc function in an adjacent column, copied that data and re-pasted it in the original cells as 'values only'.
Any ideas are appreciated!

 

[Joe4]

What exactly does your data look like?
Can you post some samples?

Note that for numbers, Excel can only retain 15 significant digits.

 

[R2ah1ze1l]

Original data: -109.29237088101
Current data: -109.29237088

Example of spreadsheet view (measurement field visually reduced to 3 decimals)

Removed

300.03​

INSP CMM C

442​

1​

-109.300​

200581EAEF2425

CMM0710-1434

Removed

300.03​

INSP CMM C

443​

1​

-109.295​

200581EAEF2426

CMM0710-1434

Removed

300.03​

INSP CMM C

444​

1​

-109.298​

200581EAEF2427

CMM0710-1434

Removed

300.03​

INSP CMM C

445​

1​

-109.308​

200581EAEF2428

CMM0710-1434

Removed

300.03​

INSP CMM C

446​

1​

-109.307​

200581EAEF2429

CMM0710-1434

Removed

300.03​

INSP CMM C

447​

1​

-109.297​

200581EAEF2430

CMM0710-1434

Removed

300.03​

INSP CMM C

448​

1​

-109.294​

200581EAEF2431

CMM0710-1434

Removed

300.03​

INSP CMM C

449​

1​

-109.313​

200581EAEF2432

CMM0710-1434

Removed

300.03​

INSP CMM C

450​

1​

-109.300​

200581EAEF2433

CMM0710-1434

Removed

300.03​

INSP CMM C

451​

1​

-109.299​

200581EAEF2434

CMM0710-1434

Removed

300.03​

INSP CMM C

452​

1​

-109.300​

200581EAEF2435

CMM0710-1434

Removed

300.03​

INSP CMM C

453​

1​

-109.309​

200581eaef2436

CMM0710-1434

Removed

300.03​

INSP CMM C

454​

1​

-109.292​

200581eaef2437

This is the example above

CMM0710-1434

Removed

300.03​

INSP CMM C

455​

1​

-109.302​

200581EAEF2438

CMM0710-1434

Removed

300.03​

INSP CMM C

456​

1​

-109.296​

200581EAEF2439

CMM0710-1434

Removed

300.03​

INSP CMM C

457​

1​

-109.291​

200581EAEF2440

CMM0710-1434

Removed

300.03​

INSP CMM C

458​

1​

-109.298​

200581EAEF2441

CMM0710-1434

Removed

300.03​

INSP CMM C

459​

1​

-109.295​

200581EAEF2455

CMM0710-1434

Removed

300.03​

INSP CMM C

460​

1​

-109.299​

200581EAEF2456

CMM0710-1434

Removed

300.03​

INSP CMM C

461​

1​

-109.292​

200581EAEF2457

CMM0710-1434

Removed

300.03​

INSP CMM C

462​

1​

-109.301​

200581EAEF2458

CMM0710-1434

 

[Joe4]

Your image did not come out.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you want to limit it to 18 characters, you could do something like this:

=LEFT(A1,18)+0

Note that you may also need to change the format of the cell, and change the number of decimal places to see the entire entry.

 

[R2ah1ze1l]

Your image did not come out.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you want to limit it to 18 characters, you could do something like this:

=LEFT(A1,18)+0

Note that you may also need to change the format of the cell, and change the number of decimal places to see the entire entry.

I thought wrapping the table with [c0de][/c0de] was the right way, I already had corrected that.
I cannot DL the XL2BB.

I will attempt the code sample you suggested.
Thank you

 

[Joe4]

I thought wrapping the table with [c0de][/c0de] was the right way, I already had corrected that.

No, the Code tags are used for posting VBA code, not table data.

Note that there is also an "Upload Image" button at the bottom of the editor.
While not as good as using XL2BB (which allows us to copy and paste into Excel), it can post images of what your data looks like.

 

[R2ah1ze1l]

The code suggestion of =LEFT(A1,18)+0 turns the values to text.
Still failed and I used 13 instead of 18.
I will wait for the helpdesk of the software.

I appreciate your efforts.

 

[Joe4]

The code suggestion of =LEFT(A1,18)+0 turns the values to text.

The LEFT function returns a Text value, but adding 0 to it should coerce it back to a number.
If it doesn't, then there is something else in your entry that is not allowing it to be converted to a number (some character other and a number, decimal point, or negative sign).

I tried it on the example you gave, and it worked for me.
Can you provide an example that doesn't work, including:
- The actual data you are applying it to
- Your expected result
- The result you are getting

 

[R2ah1ze1l]

The LEFT function returns a Text value, but adding 0 to it should coerce it back to a number.
If it doesn't, then there is something else in your entry that is not allowing it to be converted to a number (some character other and a number, decimal point, or negative sign).

I tried it on the example you gave, and it worked for me.
Can you provide an example that doesn't work, including:
- The actual data you are applying it to
- Your expected result
- The result you are getting

I had forgotten the "+0"
after retrying it now is a numeric value as you stated it should.

Still getting the exceeds 18 character limit when I am using 13 as my value.

You would most likely need access to the location I am uploading to, and I can't grant you that. Thanks again for your efforts!

 

[Joe4]

Still getting the exceeds 18 character limit when I am using 13 as my value.

Can you walk me through an actual example?
What is the value?
How/where are you trying to impose this limit?
Are you running this on existing data, or new data entry?