Capture 80% of cell

[amerifax]

Last night, I needed help with an issue that I thought was rather extreme and was not even certain if I would understand the formula. Well within one hour I was able to complete the project thanks to jasonb75.

Again I need help with an extreme issue, that logically, I'm not even certain it can be accomplished.

I need to capture 80% of the value in a cell to help identify companies when the name and address are concatenated together. Since some names are very short or extremely long dealing with the first 10 characters or similar approaches would not work effectively.

So my approach would be 80% of the name and 80% of the address concatenated into a field and that would give me a very strong identifier for the record. My problem is in the many years I have been using spreadsheets, as an end user, and databases I have never heard anyone talk of using a percentage of a value. But here's hoping.

Thanks
Bob

 

[jasonb75]

Am I reading that correctly, you just want to concatenate them to use as a primary key?

=LEFT(A2,LEN(A2)*80%)&LEFT(B2,LEN(B2)*80%)

 

[VoG]

Maybe like this

Excel Workbook
	A	B	C
1	Microsoft Corporation	One Microsoft Way, Redmond, WA	Microsoft Corpor One Microsoft Way, Redmo
Sheet2

 

[amerifax]

Both ways work for my key. What can I say? I'm not a stupid man but... I do wounder where you guys come up with this incident, don't know want to say. You seemed to come up with formulation as easy as I say "Thanks".
BOb

 

[amerifax]

I added an additional element to the formula - colored in red"
=LEFT(A2,LEN(A2)*80%)&LEFT(B2,LEN(B2)*80%)&LEFT(E2,LEN(E2)*99.9%).

E is a 10 digit zip code, including "-".

I am trying to capture five digit. Thinking 75% should give me close to five digit it did not. So I went all the way up to 99% with no luck. Writing at 100% does give me the five digits. This seems to be an inconsistency. Does it have something to do with text versus numeric?
Thanks again
Bob

 

[jasonb75]

if you want 5 digits then just set it to that.

&LEFT(E2,5)

All that LEN() is doing is count the number of characters in the cell, doesn't matter if they're text, numeric or symbolic, all are treated equal.

When you set a % of the lenth as we have done the result is rounded down, which is where the inconsistancy comes from.

Although I can't see how 100% can return 50% of the cell content, is it definitely all in 1 cell?

 

[amerifax]

I understand. Thanks for the help.
Bob