CODEWITHSUNDEEP
excelexcel-formula
Samantha
Samantha

Reputation: 75

How to insert cell values into a sentence in excel with formulas?

There is an excel table, which looks like this:

Day | Month | Year
1 | Jan | 1999
6 | no month | 2009

How can I insert cell values into a sentence like this? Scenario 1: "The person was born on" 1 "in the month of" Jan "in the year of" 1999

But if there is no month specified, the sentence should look like this:

Scenario 2: "The person was born on" 6 "without month specified" "in the year of" 2009

When the sentence is created, how can I separate the sentence into 3 different columns like this:

Column 1: "The person was born on" 6

Column2: "in the month of" Jan

Column 3: "in the year of" 1999

I have tried some functions but I could not come up with a solution that works.

Upvotes: 2

Views: 21196

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Sentence Creation with Dates

Workbook Download (Dropbox)

In Cells:

D2

="The person was born "&IF(A2="","without day specified ","on the "&A2&CHOOSE(IF(AND(A2>10,A2<14),4,IF(AND(VALUE(RIGHT(A2,1))>0,VALUE(RIGHT(A2,1))<4),VALUE(RIGHT(A2,1)),4)),"st","nd","rd","th"))

E2

=IF(B2="","without month specified",IF(A2="","in","of")&" the month of " & B2)

F2

=IF(C2="","without year specified","in the year of "&C2)

G2

=D2&" "&E2&" "&F2&"."

enter image description here

Upvotes: 2

Skin
Skin

Reputation: 11197

Assuming your table starts from cell A1, enter the following into the cells specified.

Cell D2

="The person was born on " & A2

Cell E2

=IF(B2="","without month specified","in the month of " & B2)

Cell F2

="in the year of " & C2

Cell G2

=D2 & " " & E2 & " " & F2

... now fill all of those columns down to the last row of your table and you have your result.

Upvotes: 0

Related Questions