If ... Then

[Spike9458]

I have a column with the words Lane, Drive, Road spelled out. How would I
write an if/then statement to change those words them La, Dr, and Rd. You
guys are great!

Thanks,

--Jim

 

[Felipe I]

Jim,

Try:
=IF(A1="Lane","Ln.",IF(A1="Drive","Dr.",IF
(A1="Road","Rd.","")

Or creae a table like the following:
A B
Lane Ln.
Drive Dr.
Road Rd.

And use vlookup to get the data to the column next to the
original one.

 

[Spike9458]

I tried that, but it's not working. The column has the street name as well
as whether it's a street, Lane, Drive, Road ....... so when the formula
looks for "Lane" it doesn't find it by itself, because Misty Lane is what's
in the cell. Any more thoughts?

--Jim

 

[immanuel]

You could try:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," Lane"," Ln.")," Drive", " Dr."),"
Road", " Rd.")

/i.

 

[Don Guillett]

try this macro
sub shortenem()
for each c in range("b2:b22")
if ucase(c)="LANE" then c.value="La"
if ucase(c)="DRIVE" then c.value="Dr"
if ucase(c)="ROAD" then c.value="Rd"
next
end sub

 

[Spike9458]

Immanuel, that worked perfectly! Thank-you very much!

--Jim

 

[Don Guillett]

Next time it would be nice if you fully explained your problem. Saves all
time and effort.

 

[Spike9458]

Sorry for not being more clear. I do appreciate the effort!