Replace Words from end to beginning

[BCus]

Hey MrExcel !

i have a question about replacing over 1700+ rows words..
this is what needs to be done basicly;

row1: word1/word2/word3/word4

I want to replace the words in this way, note the numbers;

word4/word2/word3

I want to replace word1 with word4, deleting word1 but keeping word4 at the beginning.

Actual word4 changes... the actual word is different on each row!
example:

row1: word1/word2/word3/apples
row2: word1/word2/word3/grapes
row3: word1/word2/word3/oranges

replace and removing the end forward slashes on each row:

row1: apples/word2/word3
row2: grapes/word2/word3
row3: oranges/word2/word3

Another scenario is to delete word1 on each row first, then just moving word4 to the beginning of the phrase and removing the forward slashes at the end.


Do you think this is possible ?
I am using MS Excel 2010,

Cheers!

 

[trunten]

Personally, I would use text to columns (found in the data tab) using the forward slash as my delimiter to separate each word into it's own column.
Then it's just a case of using a quick formula in an empty column to re-arrange the data as you see fit (eg. =D2&"/"&B2&"/"&C2). Fill that formula down your rows, copy the formula column and use paste special to paste the VALUES into your column A again, then finally delete the extra columns.

Job done.

 

[maheshrk81]

Use text to Columns and try orelse U can try with Ctrl H(Replace) i think.

 

[GlennUK]

You could do it one formula, but it's kind of messy:

Excel Workbook
	A	B
1	word1/word2/word3/apples	apples/word2/word3
2	word1/word2/word3/grapes	grapes/word2/word3
3	word1/word2/word3/oranges	oranges/word2/word3
Sheet2

 

[Sandeep Warrier]

Hi & welcome to the Board!

Assuming there are always only 4 words (i.e. 3 slashes)

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">word1/word2/word3/apples</td><td style=";">apples/word2/word3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">word1/word2/word3/grapes</td><td style=";">grapes/word2/word3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">word1/word2/word3/oranges</td><td style=";">oranges/word2/word3</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">A1,"/",REPT(<font color="Purple">" ",50</font>)</font>),50</font>)</font>)&MID(<font color="Blue">A1,FIND(<font color="Red">"/",A1</font>),FIND(<font color="Red">"/",A1,FIND(<font color="Green">"/",A1,FIND(<font color="Purple">"/",A1</font>)+1</font>)+1</font>)-FIND(<font color="Red">"/",A1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[BCus]

thanks everyone for replying, Sandeep Warrier's formula worked perfectly

I have another question in regards of removing a word..

Here is the situation:

Word1 word2 word3 ("0123456789ABCabc..")

How can i only remove the last information which is (" .... ") and keed only the Word1 word2 word3 word4 etc.., information ?

row1
Word1 word2 word3 word4 (School Work) ("0123456789ABCabc..")

row2
Word1 word2 (School Work) ("0123456789ABCabc..")

etc...

I want the data to be displayed this way:

row1
Word1 word2 word3 word4 (School Work)

row2
Word1 word2 (School Work)


ALSO, and this is the tricky part of the problem.

I want to do this, please note the word number.. i want to find the word RED within the description and move it to the front of the description, example:

row1
Word1 word2 word3 RED word5 (School Work) ("0123456789ABCabc..")

row2
Word1 word2 word3 word4 RED word6 (School Work) ("0123456789ABCabc..")

...

row1
RED word2 word3 word4 word5 (School Work)

row2
RED word2 word3 word4 word5 word6 (School Work)



the number of words per row in the description vary.


thanks

 

[BCus]

any ideas ?

 

[GlennUK]

What do you mean by "please note the word number"?

 

[BCus]

the number of words vary per row in the description.

 

[Peter_SSs]

I'm not quite sure if I have understood the last example fully, but try these formulas (each one copied down) for the various problems, including the original one.

Excel Workbook
	A	B
1
2	word1/word2/word3/apples	apples/word2/word3
3	word1/word2/word3/grapes	grapes/word2/word3
4	word1/word2/word3/oranges	oranges/word2/word3
5
6	Word1 word2 word3 word4 (School Work) ("0123456789ABCabc..")	Word1 word2 word3 word4 (School Work)
7	Word1 word2 (School Work) ("0123456789ABCabc..")	Word1 word2 (School Work)
8
9	Word1 word2 word3 RED word5 (School Work) ("0123456789ABCabc..")	RED Word1 word2 word3 word5 (School Work)
10	Word1 word2 word3 word4 RED word6 (School Work) ("0123456789ABCabc..")	RED Word1 word2 word3 word4 word6 (School Work)
Rearrange Words