matching by sting

Hello,

I have 2 excel file with

file 1

color_name
Blue my baloon b
blue & a
Blue a

file 2

color_name	color_name
blue blue	1234566
blue c	1234577

I would like to use the only first digit of the color_id(12345) and the name(blue) and match all the record from the file 1 by name(blue) and present all by the first 5 letter(12345) . would like to get this please:

color_name	color_id
Blue my baloon b	12345
blue & a	12345
Blue a	12345

Thanks

Data Investigation

Accepted answers

All comments

AngelosPachis

Hi @Borisham ,

This is the most dynamic solution I can think of. You break down the colour_name column from file 2 in individual words and then look if those are present in your file 1 input.

Workflow

Screenshot 2020-10-27 140754.jpg

Output

Screenshot 2020-10-27 140744.jpg

Give it a try and let me know, I hope that helps.

Regards,

Angelos

example.yxmd

randreag

Hi @Borisham

You can accomplish this using a formula to get the name and the number using a substring. Before try to put all in uppercase or lowercase in order that match. Then you can use the join.