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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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)
 
Thanks Bob. I get it now.

Bob Phillips said:
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)
Click to expand...
 
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
 
Thanks a whole lot!!!! Have a great weekend!!!

Len said:
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


[email protected] said:
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.
Click to expand...
Click to expand...
 
Back
Top