LEN worksheet function, why use it & what does it represent.

[Guest]

It sounds dumb & real silly, I can't figure the definition or meaning in
laymans term for the LEN function and why is it used in so many functions.
The definition in the help section doesn't say much about LEN.

For example =RIGHT(A1,LEN(A1)-3)

Please help. Thank you.

 

[Bob Phillips]

LEN is often used, as in your example, so that you can strip off leading
characters, and not lose any. By using LEN, you don't have to guess how big
a cell is, you use its actual value.

For example, say we want to strip off the first 3 characters for the
following sequence

ABC123
XYZ99999999
AAA1

We don't actually strip characters away, we return all but those characters.
But, how many characters do we allow for, 3, 7, 1? By using LEN we can
calculate the lengths (6, 10, 4) and extract all but the first 3
(LEN(A1)-3).Combine that with RIGHT(A1,that_num) and we return
123
9999999
1

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Thanks Bob. I get it now.

LEN is often used, as in your example, so that you can strip off leading
characters, and not lose any. By using LEN, you don't have to guess how big
a cell is, you use its actual value.

For example, say we want to strip off the first 3 characters for the
following sequence

ABC123
XYZ99999999
AAA1

We don't actually strip characters away, we return all but those characters.
But, how many characters do we allow for, 3, 7, 1? By using LEN we can
calculate the lengths (6, 10, 4) and extract all but the first 3
(LEN(A1)-3).Combine that with RIGHT(A1,that_num) and we return
123
9999999
1

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

I'm no expert but where a piece of data can be any length the only way to
discover its length, and to act upon it, is to use a function that will count
the number of characters. eg A1 = "This is my sample data string." [30
characters]
So, your formula LEN(A1)-3 = 27. All it's doing is a character count.

When used in =RIGHT(A1,LEN(A1)-3) It is selecting the string of data 'A1'
and excluding the last three characters. So, you get "This is my sample
data stri"

If you have a string A1 = "JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC" you can
select three characters from the string by using =MID(A1,(Month No x 3)-3,3)
which for APR (month 4) will count in twelve characters (4x3) then count
back 3 (9) then display the next three (3) characters = "APR".

I apologise if this is as clear as mud, but I only joined this queue
tonight and haven't read any learned replies yet.

Regards,

Len

 

[Guest]

Thanks a whole lot!!!! Have a great weekend!!!

I'm no expert but where a piece of data can be any length the only way to
discover its length, and to act upon it, is to use a function that will count
the number of characters. eg A1 = "This is my sample data string." [30
characters]
So, your formula LEN(A1)-3 = 27. All it's doing is a character count.

When used in =RIGHT(A1,LEN(A1)-3) It is selecting the string of data 'A1'
and excluding the last three characters. So, you get "This is my sample
data stri"

If you have a string A1 = "JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC" you can
select three characters from the string by using =MID(A1,(Month No x 3)-3,3)
which for APR (month 4) will count in twelve characters (4x3) then count
back 3 (9) then display the next three (3) characters = "APR".

I apologise if this is as clear as mud, but I only joined this queue
tonight and haven't read any learned replies yet.

Regards,

Len
--
Len

It sounds dumb & real silly, I can't figure the definition or meaning in
laymans term for the LEN function and why is it used in so many functions.
The definition in the help section doesn't say much about LEN.

For example =RIGHT(A1,LEN(A1)-3)

Please help. Thank you.