if statement to replace values

[param299]

hello experts,

Mapping:

107507-0000 314071
921570-0000 324000

check Values and replace first 11 digits (this is on a different spreadsheet):
921570-0000-50-C
921570-0000-10-c
107507-0000-30-C
107507-0000-40-C


I would like to replace values based on the mapping. for example: if the mapping is 107507-0000 to 314071 then i would like to replace 107507-0000-40-C and 107507-0000-30-C to 314071-40-C and 314071-30-C respectively.

Thanks

[Pauleyb]

Usually best to provide a spreadsheet so that any formula provided can be relevant.

So, I would attack this with a VLOOKUP, using the LEFT function to just search for the first 11 characters, then returning the data from the second column (e.g. 314071), then appending the fiver RIGHT characters to that result.

[param299]

Hi Pauley,
Thanks for your reply,
Could you please explain this with an example from the data i provided.

Regards

[ben_hensel]

Pauley was proposing something like this:

=VLOOKUP( LEFT(cell, 11), table, 2, FALSE) & RIGHT(cell, 5)