Excel - Find & Replace text in a string
 

Hi All

I have a problem here, hope the experts can help.


1 Mickey Mouse #The | The Mickey Mouse
2 World History #The | The World History
3 National History | National History
4 The National Geography | The National Geography
5 Excel for Dummies #The | The Excel for Dummies
6 Expert | Expert
7 Excel 101 | Excel 101


The above shows a list of book titles in 1st column , if i want to
remove "#The" from the back and send it to the front like 2nd column,
what would be formula? (those without #The at the back should remain as
it is)

Tq in advance :)

Regards/BKLim :confused:


--
bklim
------------------------------------------------------------------------
bklim's Profile: http://www.excelforum.com/member.php...o&userid=24268
View this thread: http://www.excelforum.com/showthread...hreadid=378873

Hi!

Isn't this covered in "Excel for Dummies" ?

Just kiddin' !

With the list in the range A1:A7.

Enter this formula in B1 and copy down:

=IF(ISERROR(FIND("#",A1)),A1,RIGHT(A1,3)&" "&LEFT(A1,FIND("#",A1)-2))

OR, use Text to ColumnsDelimited
Delimiter: #

Then, in C1 enter this formula and copy down:

=IF(B1="",A1,B1&" "&A1)

Biff

"bklim" wrote in
message ...

Hi All

I have a problem here, hope the experts can help.


1 Mickey Mouse #The | The Mickey Mouse
2 World History #The | The World History
3 National History | National History
4 The National Geography | The National Geography
5 Excel for Dummies #The | The Excel for Dummies
6 Expert | Expert
7 Excel 101 | Excel 101


The above shows a list of book titles in 1st column , if i want to
remove "#The" from the back and send it to the front like 2nd column,
what would be formula? (those without #The at the back should remain as
it is)

Tq in advance :)

Regards/BKLim :confused:


--
bklim
------------------------------------------------------------------------
bklim's Profile:
http://www.excelforum.com/member.php...o&userid=24268
View this thread: http://www.excelforum.com/showthread...hreadid=378873