Nesting functions within the IF statement in EXCEL

0 votes

I want to concatenate three fields from a spreadsheet into one (the locality column). But not often do all three columns include information. The desired result is to have a single string that ends with a period, with each column being separated by a comma.

Example data:

locality placename localityname localitydirections
Tulsa
City Park Tulsa
Overlook Chicago Ohio Turn right at the traffic light
From the house turn left at the stop light

Desired Output:

locality placename localityname localitydirections
Tulsa. Tulsa
City Park, Tulsa. City Park Tulsa
Overlook, Chicago Ohio, Turn right at the traffic light. Overlook Chicago Ohio Turn right at the traffic light.
From the house turn left at the stop light. From the house turn left at the stop light

I have tried the following code, but I am met with an error. This code assumes:

'locality' = Column A

'placename' = Column B

'localityname' = Column C

'localitydirections' = Column D

and the rows present are rows 1 - 5

First I used the formula

=B2&", "&C2&", "&D2&"." and dragged it down to auto-populate the remaining fields.

However, it was giving me the following outcome:

locality placename locality name localitydirections
, Tulsa, . Tulsa
City Park, Tulsa, . City Park Tulsa
Overlook, Chicago Ohio, Turn right at the traffic light. Overlook Chicago Ohio Turn right at the traffic light.
, , From the house turn left at the stop light. From the house turn left at the stop light

The punctuation becomes problematic with the blank fields.

What function could fix this? My initial thought was an IF function.

Apr 6, 2023 in Others by Kithuzzz
• 38,010 points
302 views

1 answer to this question.

0 votes

For Excel 2019 and above use TEXTJOIN formula with ignore_empty parameter set to TRUE:

=TEXTJOIN(", ",TRUE,B2:D2)&"."

Result:

enter image description here

answered Apr 6, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,420 points
287 views
0 votes
1 answer

How can increase the speed of if statement in VBA Code?

Use a Dictionary Object. Option Explicit Sub PreencherO() ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,420 points
335 views
0 votes
1 answer

Highlight cells in an Excel column if the value can be found in an array

Try this: =COUNTIFS(B:E,$A1) READ MORE

answered Feb 24, 2023 in Others by narikkadan
• 63,420 points
889 views
0 votes
1 answer

How to do the comand IF THEN in Excel

Try this: =SUM(IF(AND(H39<=38,H39>=20),1,0),... If so then maybe this is ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
• 38,010 points
261 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,439 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,486 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
732 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,486 views
0 votes
1 answer
0 votes
1 answer

Excel vba auto log in to hdfc bank and downloading the bank statement

That page has a bunch of nested ...READ MORE

answered Jan 6, 2023 in Others by narikkadan
• 63,420 points
461 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP