CODEWITHSUNDEEP
stringexcelvbaexcel-formulavlookup
Shoaib
Shoaib

Reputation: 65

VLOOKUP function to modify column data

I have some data, say selected IDs, in sheet A. Sheet B contains all the data and I use VLOOKUP to filter the rows in sheet B based on IDs in sheet A. I want to know if I can change the data in any column of that filtered rows. Say I have a data with ID 10:

10   0002345678

I want to change the corresponding data against this ID by adding a "$" instead of one zero so record length doesn't change:

10   00$2345678

I have a lot of entries and want to automate this process.

Can we use the REPLACE function as a parameter in the VLOOKUP, or any other solution like using VBA?

Upvotes: 0

Views: 611

Answers (1)

pnuts
pnuts

Reputation: 59485

I think you are asking whether possible "on the fly" to which the answer would be yes. For example: 

=SUBSTITUTE(VLOOKUP(A1,A!A:B,2,0),"000","00$")

If to replace last 0 with $ the following adaptation may suit: 

=SUBSTITUTE(VLOOKUP(A1,A!A:B,2,0),0,"$",LEN(VLOOKUP(A1,A!A:B,2,0))-LEN(SUBSTITUTE(VLOOKUP(A1,A!A:B,2,0),0,"")))

Upvotes: 3

Related Questions