Need to merge columns but data in different format (MB0000100 vs 0000100) - can I do it?

[ashfield]

Hello

This is my first post so please forgive me if this is a stupid question.

I am trying to merge two sheets with data for geographic regions.

My problem is the regions are coded slightly differently in the two sheets.

In one sheet, the name for each region is listed as:
MB 0000100
MB 0000200
MB 0000201 etc

In the other sheet, just the number is entered without the MB:
0000100
0000200
0000201

I am not sure that the sequence is exactly the same in both sheets, so cannot just replace the data in one column with the format from the other sheet.

Is there a simple way to recode/remove the leading MB from the first sheet, so I can analyse these sheets together?

Thank you in advance for your help

 

[mrshl9898]

Assuming the data starts in A2

=SUBSTITUTE(A2,"MB ","")

 

[ashfield]

Assuming the data starts in A2

=SUBSTITUTE(A2,"MB ","")

Thank you very much! This works, thank you for your help.

 

[jtakw]

Cross posted here: https://www.excelforum.com/excel-ge...00100-vs-0000100-can-i-do-it.html#post4994483

 

[Joe4]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.