Question description:
This user has given permission to use the problem statement for this
blog.
column name: Name
Values: either has names or blank
I want to create a new variable, which writes 'named' if the value has a name or 'unnamed' if the value is blank.
Solved by F. J. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
27/11/2017 - 04:57
welcome to excel got it pro
Excelchat Expert
27/11/2017 - 04:57
I understand you want to want to create a new variable, which writes 'named' if the value has a name or 'unnamed' if the value is blank.
Excelchat Expert
27/11/2017 - 04:58
=IF(ISBLANK(A2),"unnamed","named")
Excelchat Expert
27/11/2017 - 04:58
here is the formula
User
27/11/2017 - 04:58
oh okay. I thought that i needed to give a demo of my data
Excelchat Expert
27/11/2017 - 04:59
you did and it helped
Excelchat Expert
27/11/2017 - 04:59
:)
User
27/11/2017 - 04:59
thankyou
User
27/11/2017 - 04:59
can i as k you onw more question ?
Excelchat Expert
27/11/2017 - 04:59
sure, go ahead. though we are only allowed to solve only one question per session.
User
27/11/2017 - 05:00
I have age variable as i have shown it to you
User
27/11/2017 - 05:00
i want to work with the age variable
Excelchat Expert
27/11/2017 - 05:00
okay
User
27/11/2017 - 05:00
how do i generalise it ?
User
27/11/2017 - 05:00
for using it in my analysis
Excelchat Expert
27/11/2017 - 05:01
what do you want to do with age? I'm not sure.
User
27/11/2017 - 05:01
I am working with a animal dataset
User
27/11/2017 - 05:01
and i want the age to be either in weeks or in months or in years
User
27/11/2017 - 05:02
but the values are mixed
Excelchat Expert
27/11/2017 - 05:02
and??
Excelchat Expert
27/11/2017 - 05:02
you want to get the values automatically?
User
27/11/2017 - 05:02
how do i convert it ?
User
27/11/2017 - 05:03
i want one column that has age in months
User
27/11/2017 - 05:04
and there are 26,000 observations.I cannot convert each of them manually
User
27/11/2017 - 05:04
=if age is 1 year,then age_months =12
User
27/11/2017 - 05:04
something like this
Excelchat Expert
27/11/2017 - 05:04
age in months and years can be converted in months easily. but weeks are not that easy.
Excelchat Expert
27/11/2017 - 05:05
Since you don't have definite number of days count in weeks.
Excelchat Expert
27/11/2017 - 05:06
I mean 2 weeks can be 8 days or 13 days.
User
27/11/2017 - 05:07
forget weeks
User
27/11/2017 - 05:07
but what about months and years
Excelchat Expert
27/11/2017 - 05:07
that is still doable but will need time.
User
27/11/2017 - 05:07
how should i convert '2 years' to '24 months'
Excelchat Expert
27/11/2017 - 05:07
let me have a go at it.
User
27/11/2017 - 05:07
okay okay
User
27/11/2017 - 05:07
:)
Excelchat Expert
27/11/2017 - 05:11
do you have both year/years value?
Excelchat Expert
27/11/2017 - 05:12
or just one of them?
User
27/11/2017 - 05:13
both
Excelchat Expert
27/11/2017 - 05:13
but don't worry, I'm working on it.
User
27/11/2017 - 05:14
the column that i have highlighted
User
27/11/2017 - 05:14
tell me the logic for it
User
27/11/2017 - 05:14
so i can develop the rest of it on my own
Excelchat Expert
27/11/2017 - 05:14
which one of it?
User
27/11/2017 - 05:14
H
Excelchat Expert
27/11/2017 - 05:15
you can see the formula on H2 that i'm working on.
Excelchat Expert
27/11/2017 - 05:15
=IF(or(mid(E2,search(" ",E2)+1,10)="year",mid(E2,search(" ",E2)+1,10)="years"),value(left(E2,2))*12,"not")
Excelchat Expert
27/11/2017 - 05:15
this is for the year/years part.
User
27/11/2017 - 05:15
give me a formula for this - if E2 contains "year" then copy the contents to a new column
Excelchat Expert
27/11/2017 - 05:16
=IF(or(mid(E2,search(" ",E2)+1,10)="year",mid(E2,search(" ",E2)+1,10)="years"),value(left(E2,2))*12,"not")
User
27/11/2017 - 05:16
shit its huge
User
27/11/2017 - 05:16
could you explain ?
Excelchat Expert
27/11/2017 - 05:16
yeap, and we also need to incorporate months in it too
Excelchat Expert
27/11/2017 - 05:17
sure.
Excelchat Expert
27/11/2017 - 05:18
=IF(or(mid(E2,search(" ",E2)+1,10)="year",mid(E2,search(" ",E2)+1,10)="years"),value(left(E2,2))*12,if(mid(E2,search(" ",E2)+1,10)="months",value(left(E2,2)),E2))
Excelchat Expert
27/11/2017 - 05:18
here's the complete formula
Excelchat Expert
27/11/2017 - 05:19
I'm explaining it
Excelchat Expert
27/11/2017 - 05:19
you are already aware of the IF formula, right?
User
27/11/2017 - 05:19
yes
Excelchat Expert
27/11/2017 - 05:20
so, here we are combining if formula with left and value formula
Excelchat Expert
27/11/2017 - 05:20
left formula extracts certain texts from strings.
Excelchat Expert
27/11/2017 - 05:20
value formula converts texts that appears to be strings.
Excelchat Expert
27/11/2017 - 05:21
I have also applied mid formula which also extracts texts from strings.
Excelchat Expert
27/11/2017 - 05:21
now details.
Excelchat Expert
27/11/2017 - 05:21
any question for above statements?
User
27/11/2017 - 05:22
no
Excelchat Expert
27/11/2017 - 05:22
"value formula converts texts that appears to be strings." made a mistake here. value formula converts texts to numbers that appears to be numbers.
User
27/11/2017 - 05:22
ohh
Excelchat Expert
27/11/2017 - 05:22
so, firstly mid function
Excelchat Expert
27/11/2017 - 05:24
mid function extracts texts from the middle of strings. syntax is =mid(string, start number, number of characters)
User
27/11/2017 - 05:24
okayyy
Excelchat Expert
27/11/2017 - 05:25
in this case =mid(E2,search(" ",E2)+1,10) will extract year/years/months from E2
User
27/11/2017 - 05:25
could you explian it along with the example
User
27/11/2017 - 05:25
it would be easier
User
27/11/2017 - 05:26
search(" ",E2) - this searches for a space in E2 . Right ?
Excelchat Expert
27/11/2017 - 05:26
do you remember the syntax =mid(=mid(string, start number, number of characters)
Excelchat Expert
27/11/2017 - 05:27
right. and returns the character position of space from E2
User
27/11/2017 - 05:27
i am getting confused
Excelchat Expert
27/11/2017 - 05:27
don't be now. :) we are just getting started.
User
27/11/2017 - 05:27
okay so search returns te position's number
Excelchat Expert
27/11/2017 - 05:27
yes.
User
27/11/2017 - 05:28
ohh okayy
User
27/11/2017 - 05:28
sp mid will extraxt text from the position number that search has gived
User
27/11/2017 - 05:28
given*
User
27/11/2017 - 05:29
and we have added one...so that it extracts 10 letters after the space
Excelchat Expert
27/11/2017 - 05:29
the position of space in E2 is 2. we added one with this number. so now its 3. search(" ",E2)+1 ... 3 is the character position from which year
Excelchat Expert
27/11/2017 - 05:29
that's correct.
User
27/11/2017 - 05:29
okayy
Excelchat Expert
27/11/2017 - 05:30
we also used an or function here
Excelchat Expert
27/11/2017 - 05:30
or is a logical function that returns true if one of the arguments are true
Excelchat Expert
27/11/2017 - 05:30
so mid function returns year/years
Excelchat Expert
27/11/2017 - 05:31
now if this is true then we get to the value(left(E2,2))*12 part
Excelchat Expert
27/11/2017 - 05:31
left function returns text from the left of a string.
Excelchat Expert
27/11/2017 - 05:32
syntax is =left(string, number of characters)
User
27/11/2017 - 05:32
right
Excelchat Expert
27/11/2017 - 05:32
as you can see left(E2,2) will return first two characters from E2.
User
27/11/2017 - 05:32
yes got it
User
27/11/2017 - 05:32
what is the value function about?
Excelchat Expert
27/11/2017 - 05:33
but the returned character will be text and we can't multiply text values.
Excelchat Expert
27/11/2017 - 05:33
hence we use value function to convert texts to numbers.
User
27/11/2017 - 05:33
ohh right
Excelchat Expert
27/11/2017 - 05:34
are you still confused?
User
27/11/2017 - 05:34
nope
User
27/11/2017 - 05:34
basically
User
27/11/2017 - 05:34
we searched for a blank space
User
27/11/2017 - 05:34
extracted the string after that
User
27/11/2017 - 05:34
then we used left to extract left part that is the number
User
27/11/2017 - 05:34
but it return text
User
27/11/2017 - 05:35
so we use value functin to to convert it into a number
User
27/11/2017 - 05:35
and then we multiply it by 12
Excelchat Expert
27/11/2017 - 05:35
yes.
User
27/11/2017 - 05:35
but i have a doubt
User
27/11/2017 - 05:35
how did we use OR
User
27/11/2017 - 05:35
i man when
Excelchat Expert
27/11/2017 - 05:36
we need both 'year' or 'years' and multiply it by 12 right?
User
27/11/2017 - 05:36
so if the value is year or years,then multiply by 12
Excelchat Expert
27/11/2017 - 05:36
yeap.
User
27/11/2017 - 05:37
oh great
Excelchat Expert
27/11/2017 - 05:37
thanks, in that case I humbly ask you to close the session. session allowed time is 20 mins for a question. I have answered your first question and also solved the second one.
User
27/11/2017 - 05:37
thankyou for your kind gesture
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
