Home

Help with application of text string formulas

Quinn Fung-A-Wing
Occasional Contributor

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 Replies

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 

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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies