Reformat String to a new format using a custom formula

[geomic788]

Hello guys.
I need a custom formula to make some reformating.

The current format is as follows:

AE.PR.04.484S
or another example
AE.EM.23.320s7

I need those to be reformated in 4484D and 23320s7
There is a huge number of Product codes but the format is the same.So the general rules are:

Always remove the "AE.PR." or "AE.EM." part
Always remove ther dot after the first 2 numbers (04"." or 23"." )
If number starts with a zero (as in 04) remoze the zero
Keep the letter in the end

For better understanding the format can be explained as follows:

yy.xxx.ww

yy=04 or 23
xxx=484 or 320
ww=D or s7

Thank you in advance guys!

 

[Andrew Poulsom]

For the examples given:

=MID(A1,7,2)+0&MID(A1,10,255)

 

[Jonmo1]

I'm assuming the first one is supposed to be 4484S

Try
=MID(SUBSTITUTE(A1,".",""),5,5)+0&MID(A1,13,2)

 

[Rick Rothstein]

If the number of dot-delimited fields can vary, or if the number of characters in those dot-delimited fields can vary, then you might need something like this...

=1*TRIM(LEFT(RIGHT(SUBSTITUTE("."&A1,".",REPT(" ",99)),198),99))&TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))

 

[geomic788]

Thank you very much for your replies and im really sorry for the delay but i just saw it.I need to implement this in VBA code in order to make it automated.
So what i need to do is replace Column A that includes the codes in format AE.PR.02.004S and reformat it to 2004S with the function
=MID(A1,7,2)+0&MID(A1,10,255)

I need to create a subroutine that will do it in my main macro.

Could someone please provide the code ?
thank you all!!!

 

[Andrew Poulsom]

Can't you use the formula then Copy and Paste Special Values?

 

[geomic788]

Can't you use the formula then Copy and Paste Special Values?

Well a new excel project will be created each time i export my sales from my sales program so i created an excel project that copies the data from the draft export.Its like a host excel used to calculate.Since im gonna be using this like 20 times a day i would have to copy and paste the function every time.This is why i need it within the code so it formats in the right way by it self.

 

[Andrew Poulsom]

You can do what I suggested in a macro. The macro recorder should give you some code.