Replacing cells based on match of first letters

[djfreex]

I have a long list of customer names in a excel sheet. However, creator didnt use validation and names are entered without structure. Example:

Test & Son Co
Test & Son Co.
Test&Son Company
Test Son Comp
...

I want to be able to take the first name found and match it to first x letters of other entries and if first x letters match, it should replace the one of names to all the other cells.

Is this possible?

 

[Akashwani]

Hi,

Welcome to MrExcel.

This probably isn't the best solution for you, but it has bumped you back up if nothing else.

If you can add another column then try something like this....

Excel Workbook
	A	B
1	Name	Solution
2	Test & Son Co	Test & Son Co
3	Test& Son Co	Test & Son Co
4	Test&Son Co	Test & Son Co
5	Test and Son Co	Test & Son Co
6	Test *Son Co	Test & Son Co
7	ABCD & Son Co	ABCD & Son Co
8	ABCD & Son Co	ABCD & Son Co
9	ABCD & Son Co	ABCD & Son Co
10	ABCD& Son Co	ABCD & Son Co
11	ABCDSon Co	ABCD & Son Co
12	Your Company	Your Company
13	Your- Company	Your Company
14	Your * Company	Your Company
15	Your/Company	Your Company
16	YourCompany	Your Company
Sheet1

The formula in B3 needs to be copied down.
You can then highlight column B Copy and Paste Special Values only to column A.

This formula will fail if you have different companies that have the same first x letters....

Excel Workbook
	A	B
1	Name	Solution
2	Test & Son Co	Test & Son Co
3	Test& Son Co	Test & Son Co
4	Test&Son Co	Test & Son Co
5	Test and Son Co	Test & Son Co
6	Test *Son Co	Test & Son Co
7	ABC & Son Co	ABC & Son Co
8	ABC &Son Co	ABC & Son Co
9	ABCD & Son Co	ABC & Son Co
10	ABCD& Son Co	ABC & Son Co
11	ABCDSon Co	ABC & Son Co
12	Your Company	Your Company
13	Your- Company	Your Company
14	Your * Company	Your Company
15	Your/Company	Your Company
16	YourCompany	Your Company
Sheet1

I have highlighted the original company name and you can see that the formula fails with company ABCD.

I hope this helps or at least gets you some other replies.

Good luck.

Ak