Stock card renaming

[makinmomb]

I have this

="MML "& SUBSTITUTE(MID(B1,FIND("X",B1)-3,LEN(B1)),"= ","= "&MID(B1,1,FIND(" ",B1,1)-1)&" ")

That converts

ARIEL WALL 25 X 35 = SNOW WHITE WM290A

<colgroup><col width="363"></colgroup><tbody>
</tbody>

to

MML 25 X 35 = ARIEL SNOW WHITE WM290A


I now want the above to be ( shift last word to second word after = )

MML 25 X 35 = ARIEL WM290A SNOW WHITE

<colgroup><col width="64"></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[ztodd]

doing it all in one formula can get a bit hard- I would break this down into pieces. Add a column for "last word" (do a web search for "excel formula last word"), a separate column for "first word", a separate column for "Dimensions", and one more column for "After equal sign, before last word". Then you'll have all your pieces to concatenate together.

 

[MARK858]

No doubt someone will come up with a better / tidier solution.

Excel Workbook
	B	C	D
1	ARIEL WALL 25 X 35 = SNOW WHITE WM290A		MML 25 X 35 = ARIEL WM290A SNOW WHITE
Sheet1

 

[makinmomb]

Mark does it work your end , I copy pasted , its not working

 

[MARK858]

Mark does it work your end

What you see it post #3 is taken directly from my spreadsheet and so yes the formula in D1 works at my end (and I just copied the formula from the forum into another cell and got the same result).

 

[Snakehips]

Here is another slightly shorter option...

Excel 2007
	B	C	D
1	ARIEL WALL 25 X 35 = SNOW WHITE WM290A		MML 25 X 35 = ARIEL WM290A SNOW WHITE
Sheet7

Cell Formulas
Range	Formula
D1	=TRIM(SUBSTITUTE(SUBSTITUTE("MML "& SUBSTITUTE(MID(B1,FIND("X",B1)-3,LEN(B1)),"= ","= "&MID(B1,1,FIND(" ",B1,1)-1)&" "),TRIM(LEFT(SUBSTITUTE(B1," ",REPT(" ",99)),99)),TRIM(LEFT(SUBSTITUTE(B1," ",REPT(" ",99)),99)&RIGHT(SUBSTITUTE(B1," ",REPT(" ",99)),99))),TRIM(RIGHT(SUBSTITUTE(B1," ",REPT(" ",99)),99)),"",2))

 

[ztodd]

I recommend you follow my advice to break it into smaller steps that are easier to understand, produce the formula yourself rather than use someone else's code that you don't know how it works.