Help with application of text string formulas

Quinn Fung-A-Wing
Occasional Contributor


Can anyone help me with a formula to adjust the partnumbers in the 2nd column to the format in the 1st column? I've used text to column and some text string formulas on the 2nd column but I'm not getting close enough to the format in the 1st column to reduce the number of manual adjustments I need to make. A succesful match would be confirmed by a vlookup between 1st and 2nd column. The number of errors would need to be minimal. The errors (mismatches) would need to be corrected manually. 


Thanks in advance! Hope anyone can help.

2 Replies



The problem in the desired format is not consistent.

But you can use this formula to get as much similarity as possible:

=REPLACE(REPLACE(REPLACE(B2,4,0," "),8,0," "),12,0," ")


Also, you can use the MATCH function on top of it to get the row number of the matched cell in the desired format column:

=MATCH(REPLACE(REPLACE(REPLACE(B2,4,0," "),8,0," "),12,0," "),A:A,0)


Hope that helps


@Quinn Fung-A-Wing 

If your goal is only to match between the two columns, you can make the Desired Format as the Old format in a helper column using this formula:

=SUBSTITUTE(A2," ","")

Then use the MATCH function on the new column.

This is will reduce the unmatched cells.


Please find this in the attached file.


Hope that helps


@Quinn Fung-A-Wing 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies