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

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

Hi,

If the Text part is Always 2 characters, use B1 formula.
If the Text part is variable in length, use C1 formula.

Either formula copied down:

Excel 2016 (Windows) 64 bit

	A	B	C
1	MB 0000100	0000100	0000100
2	MB 0000200	0000200	0000200
3	MB 0000201	0000201	0000201
4	MBAB 0000202	B 0000202	0000202

Sheet: Sheet110

Excel 2016 (Windows) 64 bit

	B	C
1	=MID(A1,4,99)	=MID(A1,FIND(" ",A1)+1,99)

Sheet: Sheet110

This is perfect! Thank you so much for your help, you have just saved me days of work.