how do I substitute text in Excel?

G

Geordie

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).
 
P

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
 
D

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.
 
G

Geordie

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

Geordie

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.
 
G

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
 
B

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top