If then

  • Thread starter Thread starter Spike9458
  • Start date Start date
S

Spike9458

I'm back ... working on a mailing list database. Column A is first owner
firstname, Column B is second owner firstname, Column C is last name. I'm
using some formulas some of you helped me with earlier to get proper
capitalization, and switch first and last name around. Here's what I am
trying to do now:

Formula for ColC =PROPER((RIGHT(A3,LEN(A3)-FIND(" ",A3))))
Formula for ColD =PROPER((RIGHT(B3,LEN(B3)-FIND(" ",B3))))
These formulas give me these results:

ColA ColB ColC ColD
ColE
SMITH, JOE E. SMITH, MARY J. Joe E Mary J Smith

What I want to do is insert a column between D and E (I know how to do
that), with a formula so that if there is a name in ColD, it will return a
"&" symbol, otherwise leave it blank.

Thanks,

--Jim
 
Sorry ..... text is a little messed up ... ColE is the Last Name, in this
case, Smith. I want a formula that will recognize if there's any data in
column B to give me a & in a column (not inserted yet) between C and D.

This is so that when I print my labels it will say Joe E & Mary J Smith etc
.... making sense yet? I'm kinda dense when it comes to these formulas.

I tried the one you wrote and it returned a & to all cells, but by inserting
a space between between the first set of " " it worked perfectly. =IF(D3="
","","&").

Thanks Loads Andy,

--Jim
 
Jim,

How about trying this in C3 and forget D3?

=IF(A3="","",PROPER((RIGHT(A3,LEN(A3)-FIND("
",A3)))))&IF(AND(A3<>"",B3<>"")," & ",
"")&IF(B3<>"",PROPER(RIGHT(B3,LEN(B3)-FIND(" ",B3))),"")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
and forget inserting another column!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
THis is a great formula, but it removes the first character in A3,
so instead of Adams, I get Dams, plus it still leaves the last name first.

--Jim
 
Jim,

How about this then?

=IF(A3="","",PROPER((RIGHT(A3,LEN(A3)-FIND(",",A3)))))&IF(AND(A3<>"",B3<>"")
," & ","")&IF(B3<>"",PROPER(RIGHT(B3,LEN(B3)-FIND(" ",B3))),"") &PROPER(
LEFT(A3,FIND(",",A3)-1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
We're 98% there ... it does it all, except when there is a blank in Column
B, it still returns a & symbol. I looked at the formula, but don't know
enough about it to figure it out.

--Jim
 
I love the formula ... all the work done in one shot. I can't figure out
though, how to get a space in. My result is Joe E. & Mary J.Smith

Note there's no space between the J. and Smith.

Thanks,

--Jim
 
Jim,

This will take care of the space.

The problem you get with B3 being empty, I don't get that problem. If B3 is
empty, I get this in C3, ' Joe E. Smith'.

Regards

Bob
 
Helps if I include the formula I suppose!

=IF(A3="","",PROPER((RIGHT(A3,LEN(A3)-FIND(",",A3)))))&IF(AND(A3<>"",B3<>"")
," & ","") &IF(B3<>"",PROPER(RIGHT(B3,LEN(B3)-FIND(" ",B3))),"")&"
"&PROPER( LEFT(A3,FIND(",",A3)-1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top