how do I substitute text in Excel?

[Guest]

I need to convert imported data to plain English. In one column there are
rows containing one of four values (either A,B, C or D), which I want to
replace with their descriptors (Spring, Summer, Autumn, Winter).

 

[Pete_UK]

Highlight the column and CTRL-H (Find & Replace), then:

Find What: A
Replace With: Spring
Click Replace All

With the range still highlighted, do CTRL-H again, and:

Find What: B
Replace With: Summer
Click Replace All

and again to change C into Autumn, and finally to change D into
Winter.

Hope this helps.

Pete

 

[Don Guillett]

You should post your layout. Are you really saying A,B,C,D or is it
something else. Whole word in cell or part of a string? etc.

 

[Guest]

Thanks, but I'm looking for a quicker way, like a formula I can use that
would substitute all values at once.

 

[Guest]

I have a column with various values in random order, e.g,:
A
B
A
D
C
B
etc (for another 300 rows).
I want to replace each letter with a word (A to equal "Spring", B to equal
"Summer", C to equal "Autumn", D to equal "Winter"). I'm looking for a
formula which will do this in one step, rather than using find & replace.
This is raw data which i want to convert to use in a mailmerge.

 

[Gord Dibben]

In an adjacent column enter this formula and drag/copy down.

=LOOKUP(A1,{"A","B","C","D"},{"Spring","Summer","Autumn","Winter"})


Gord Dibben MS Excel MVP

 

[BIG D]

In an adjacent column enter this formula and drag/copy down.

=LOOKUP(A1,{"A","B","C","D"},{"Spring","Summer","Autumn","Winter"})

Gord Dibben MS Excel MVP



- Show quoted text -

IF YOU TAKE GORD DIBBEN'S FORMULA AND EXPAND IT TO THE FOLLOWING IF
STATEMENT IT WILL ERROR TRAP ANY LETTERS GREATER THAN D GIVING A BLANK
CELL INSTEAD OF "WINTER"

=IF(A1>"D","",LOOKUP(A1,{"A","B","C","D"},
{"Spring","Summer","Autumn","Winter"}))

HOPE THIS HELPS

 

[Guest]

that does the trick superbly. Thanks, really appreciated.

 

[Gord Dibben]

Good point Big D


Gord

IF YOU TAKE GORD DIBBEN'S FORMULA AND EXPAND IT TO THE FOLLOWING IF
STATEMENT IT WILL ERROR TRAP ANY LETTERS GREATER THAN D GIVING A BLANK
CELL INSTEAD OF "WINTER"

=IF(A1>"D","",LOOKUP(A1,{"A","B","C","D"},
{"Spring","Summer","Autumn","Winter"}))

HOPE THIS HELPS