Help with application of text string formulas

[Quinn Fung-A-Wing]

Hi,

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]

Hi,

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 

[#3]

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