Replace text in a cell containing a string with another string

Question

In Excel, how can I replace text in a cell containing a particular string, with another string?

The original values look like this

What I'm trying to do is replace all cells with 802 as the first 3 characters with the Text Vehicle Loans, 103 with the text Home Loans and 303 with Saving Accounts, 502 with Other Loans

So the end result is

Vehicle Loans
Vehicle Loans
Vehicle Loans
Home Loans
Home Loans
Savings Accounts
Savings Accounts
Other Loans

I tried with the Substitute Function, but it seems that function won't work for something like this.

What other way to achieve this?

Jonathan · Accepted Answer · 2017-02-21 07:13:22Z

1

Try combining IF and LEFT:

=IF(LEFT(A1,3)="802","Vehicle loans", IF(LEFT(A1,3)="103","Home loans",IF(LEFT(A1,3)="303","Saving account",IF(LEFT(A1,3)="502","Other account","Unknown account"))))

Use in an adjoining cell and copy and paste values.

answered Feb 21, 2017 at 7:13

Jonathan

1,0251 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2017-02-21 12:31:37Z

1

I wquld use a formula and a lookup table.

Construct your ReplacementTable

Then, with your data starting in A2, try this formula:

=VLOOKUP(--LEFT(A2,FIND("-",A2)-1),ReplacementTable,2,FALSE)

ReplacementTable Refers To: =Sheet1!$E$2:$F$6

Since I entered the "prefix" as a number in the lookup table, I have to use a number to do the lookup -- hence the double unary -- preceding the LEFT function in the formula.

answered Feb 21, 2017 at 12:31

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Comments

garyfairhead · Accepted Answer · 2017-02-23 11:29:12Z

0

Both the answers above are excellent.

Or you could just use search and replace. It's quick and dirty.

Ctrl-H
Find what: "802-"
Put the hyphen in so that it doesn't find values within the cell.
Replace with: "Vehicle Loans"
Repeat for the other codes you want to replace: 103, 303, etc

edited Feb 23, 2017 at 11:29

answered Feb 21, 2017 at 11:23

garyfairhead

13 bronze badges

Collectives™ on Stack Overflow

Replace text in a cell containing a string with another string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related