COMBINGING IF AND &

Jewells0905

New Member
Joined
Mar 10, 2024
Messages
42
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi,

I am trying to write a formula to generate either Ms. or Mr. based off the gender in column C, then the address for each person, I have =A2&" "&B2&" "&D2&" "&E2&" "&F2&" "&G2 which generates the first, last name, and address, and I have =IF(C2="F","Ms.", "Mr.") which works to generate either Ms or Mr, however, when i try to combine them I get an error message, or #N/A stating too many arguments.

=IF(C2="F","Ms.","Mr.",A2&" "&B2&" "&D2&" "&E2&" "&F2&" "&G2) ---- gives #N/A - IN BELOW
=CONCATENATE(IF(C2="F","Ms.", "Mr.")A2," ",B2," ",D2," ", E2, " ",F2, " ", G2) ----- parse error
=CONCATENATE(IF(C2="F","Ms.", "Mr.")C2,",",A2," ",B2," ", D2," ",E2," ",F2," ",G2) ---- parse error
=IF(C9="F","Ms.", "Mr."),CONCATENATE(A9," ",B9," ",D9," ",E9," ",F9," ",G9) ----- parse error

In the below, I have

MarySmithf787MadisonLee's SummitMO#N/A
RobertJohnsonm3728HilltopBlue SpringsMOMary Smith 787 Madison Lee's Summit MO
JohnWilliamsm8555AspenLee's SummitMO
LindaBrownf9299SunriseRaytownMO
DavidJonesm1205MapleBlue SpringsMO
BarbaraGarciaf4926CardinalLee's SummitMO
SusanMillerf7190RailroadRaytownMO
JosephDavism1301LakeviewGrain ValleyMO
ThomasRodriguezm868ChestnutGladstoneMO
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
How about
Fluff.xlsm
ABCDEFGH
1
2MarySmithf787MadisonLee's SummitMOMs Mary Smith 787 Madison Lee's Summit MO
3RobertJohnsonm3728HilltopBlue SpringsMOMr Robert Johnson 3728 Hilltop Blue Springs MO
4JohnWilliamsm8555AspenLee's SummitMOMr John Williams 8555 Aspen Lee's Summit MO
5LindaBrownf9299SunriseRaytownMOMs Linda Brown 9299 Sunrise Raytown MO
6DavidJonesm1205MapleBlue SpringsMOMr David Jones 1205 Maple Blue Springs MO
7BarbaraGarciaf4926CardinalLee's SummitMOMs Barbara Garcia 4926 Cardinal Lee's Summit MO
8SusanMillerf7190RailroadRaytownMOMs Susan Miller 7190 Railroad Raytown MO
9JosephDavism1301LakeviewGrain ValleyMOMr Joseph Davis 1301 Lakeview Grain Valley MO
10ThomasRodriguezm868ChestnutGladstoneMOMr Thomas Rodriguez 868 Chestnut Gladstone MO
Sheet6
Cell Formulas
RangeFormula
H2:H10H2=TEXTJOIN(" ",,IF(C2="f","Ms","Mr"),CHOOSECOLS(A2:G2,1,2,4,5,6,7))
 
Upvote 0
Solution
How about
Fluff.xlsm
ABCDEFGH
1
2MarySmithf787MadisonLee's SummitMOMs Mary Smith 787 Madison Lee's Summit MO
3RobertJohnsonm3728HilltopBlue SpringsMOMr Robert Johnson 3728 Hilltop Blue Springs MO
4JohnWilliamsm8555AspenLee's SummitMOMr John Williams 8555 Aspen Lee's Summit MO
5LindaBrownf9299SunriseRaytownMOMs Linda Brown 9299 Sunrise Raytown MO
6DavidJonesm1205MapleBlue SpringsMOMr David Jones 1205 Maple Blue Springs MO
7BarbaraGarciaf4926CardinalLee's SummitMOMs Barbara Garcia 4926 Cardinal Lee's Summit MO
8SusanMillerf7190RailroadRaytownMOMs Susan Miller 7190 Railroad Raytown MO
9JosephDavism1301LakeviewGrain ValleyMOMr Joseph Davis 1301 Lakeview Grain Valley MO
10ThomasRodriguezm868ChestnutGladstoneMOMr Thomas Rodriguez 868 Chestnut Gladstone MO
Sheet6
Cell Formulas
RangeFormula
H2:H10H2=TEXTJOIN(" ",,IF(C2="f","Ms","Mr"),CHOOSECOLS(A2:G2,1,2,4,5,6,7))
worked perfectly! thank you
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 1

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top