Correction in Vehicle Number

[Shabbo]

Dear Sir,

I have system generated Vehicle Number which are not matching with the correct number, I have mentioned correct Vehicle number in column B.
I wanted to match /convert the wrong vehicle numbers with correct one.

 

[AlanSidman]

=RIGHT(A2,2) & MID(A2,7,2) & " " & MID(A2,5,2) & " " & LEFT(A2,4) and copy down

 

[Shabbo]

=RIGHT(A2,2) & MID(A2,7,2) & " " & MID(A2,5,2) & " " & LEFT(A2,4) and copy down

Dear Sir,
Thanks for your reply, but its not working in second case

Wrong Vehicle Number​	Correct Number​	Formula​
3051JK04AB​	AB04 JK 3051​	AB04 JK 3051​
3211AB04FU​	AB04 FU 3211​	FU04 AB 3211​

 

[shrivallabha]

Dear Sir,
Thanks for your reply, but its not working in second case

Wrong Vehicle Number​	Correct Number​	Formula​
3051JK04AB​	AB04 JK 3051​	AB04 JK 3051​
3211AB04FU​	AB04 FU 3211​	FU04 AB 3211​

Alan's formula expects your data to be in certain format. Your data is inconsistent so you need to provide rule for placing the alphabet pairs e.g. it will always be AB and therefore the other pair shall be placed after placing 2 digits. Defining problem clearly is the first requisite when you are looking for solutions online.

 

[Shabbo]

Alan's formula expects your data to be in certain format. Your data is inconsistent so you need to provide rule for placing the alphabet pairs e.g. it will always be AB and therefore the other pair shall be placed after placing 2 digits. Defining problem clearly is the first requisite when you are looking for solutions online.

Dear Sir,
Thanks for your reply, yes it will like "AB04 FU 3211 ".

 

[shrivallabha]

Dear Sir,
Thanks for your reply, yes it will like "AB04 FU 3211 ".

So does it mean it will always be Text AB or there is any other possibility?

 

[Shabbo]

So does it mean it will always be Text AB or there is any other possibility?

AB only sir.

 

[shrivallabha]

AB only sir.

Then following formula shall work.
="AB"&MID(A2,7,2)&" "&MID(A2,IF(MID(A2,5,2)="AB",9,5),2)&" "&MID(A2,1,4)