**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.*