ExcelBanter - If Then statement for changing Values

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - If Then statement for changing Values (https://www.excelbanter.com/excel-worksheet-functions/141173-if-then-statement-changing-values.html)

If Then statement for changing Values

I am trying to format data that will be entered into a column. The data is
formated in such a way that COLORADO SPRGS shows up as Colorado Springs . I
need the full word but Im working with to many records to Find=Replace. I
wrote a If/Then that will take COLORADO SPRGS and make it Colorado Springs
but I have to do it for about 5 to 6 other abbreviations. '

=IF(PROPER('Customer Addresses'!F2)= "Colorado Sprgs","Colorado
Springs",PROPER('Customer Addresses'!F2))

I would like to write a If/Else/then statement if at all possible ....
unless of course im taking the long road on this one.

----I just couldnt find any syntax for what im trying to do

If Then statement for changing Values

You already have an IF/Then/Else statement, and you could extend the
Else clause by making it another IF/Then/Else, like so:

=IF(PROPER('Customer Addresses'!F2)= "Colorado Sprgs","Colorado
Springs",IF(PROPER('Customer Addresses'!F2)= "San Fran","San
Francisco",PROPER('Customer Addresses'!F2)))

which will give you two abbreviations to change. You can carry on
nesting IF statements in this manner, but there is a limit of 7 nested
functions which Excel will allow (plus, the formula becomes quite
long).

An alternative approach is to set up a little translation table
somewhere, made up of your abbreviations and what you want to change
them into, for example:

Colorado Sprgs Colorado Springs
San Fran San Francisco
N Yk New York
N J New Jersey

Suppose you put this table in cells X1:Y4 on the same sheet as your
formula. Then you can use a simple VLOOKUP formula to translate the
abbreviation into the longer form, such as:

=IF(ISNA(VLOOKUP(PROPER('Customer Addresses'!F2),X$1:Y
$4,2,0)),PROPER('Customer Addresses'!F2,VLOOKUP(PROPER('Customer
Addresses'!F2),X$1:Y$4,2,0))

Of course, if the formula is in the 'Customer Addresses' sheet, then
you don't really need to include the sheet name in the formula at all.
With this approach you can have very many abbreviations - all you need
to do is make the range X$1:Y$4 in the formula cover the extent of
your table. Then you can copy the formula down for as many rows as you
need.

Hope this helps.

Pete

On May 1, 11:50 pm, Noncentz303
wrote:
I am trying to format data that will be entered into a column. The data is
formated in such a way that COLORADO SPRGS shows up as Colorado Springs . I
need the full word but Im working with to many records to Find=Replace. I
wrote a If/Then that will take COLORADO SPRGS and make it Colorado Springs
but I have to do it for about 5 to 6 other abbreviations. '

=IF(PROPER('Customer Addresses'!F2)= "Colorado Sprgs","Colorado
Springs",PROPER('Customer Addresses'!F2))

I would like to write a If/Else/then statement if at all possible ....
unless of course im taking the long road on this one.

----I just couldnt find any syntax for what im trying to do

If Then statement for changing Values

I like all of these ideas.... Man I never knew excel had so many options for
the same goal. I guess im thinking old school lol. Don I like your idea for
the substitute function but the pivot table will give some others a chance to
add and remove entries for the pivot table.

Thanks guys for the solution to my problem

"Don Guillett" wrote:

For multiples you can nest it
=SUBSTITUTE(SUBSTITUTE(G5,"ddddd","Don"),"sprgs"," Springs")

Sub replacetexts()
With Range("f2:f22")
.Replace "ddddd", "Don"
.Replace "sprgs", "Springs"
.etc
End With
End Sub
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
I would use a macro but a formula using this idea would be OK
=SUBSTITUTE(G3,"sprgs","Springs")

--
Don Guillett
SalesAid Software

"Noncentz303" wrote in message
...
I am trying to format data that will be entered into a column. The data is
formated in such a way that COLORADO SPRGS shows up as Colorado Springs .
I
need the full word but Im working with to many records to Find=Replace. I
wrote a If/Then that will take COLORADO SPRGS and make it Colorado
Springs
but I have to do it for about 5 to 6 other abbreviations. '

=IF(PROPER('Customer Addresses'!F2)= "Colorado Sprgs","Colorado
Springs",PROPER('Customer Addresses'!F2))

I would like to write a If/Else/then statement if at all possible ....
unless of course im taking the long road on this one.

----I just couldnt find any syntax for what im trying to do

If Then statement for changing Values