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=378874

Excel - Find & Replace text in a string
 

If your text is in A1, then use:
=IF(ISNUMBER(FIND("#The",A1)),"The
"&TRIM(LEFT(A1,FIND("#The",A1)-1)),A1)

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378874

Excel - Find & Replace text in a string
 

Hi Mangesh

Tq vry much for the prompt reply, and i tried it and found another
problem, it will not process title with #the but #The. Please advise.

Regards/BKLim



mangesh_yadav Wrote:
If your text is in A1, then use:
=IF(ISNUMBER(FIND("#The",A1)),"The
"&TRIM(LEFT(A1,FIND("#The",A1)-1)),A1)

Mangesh


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

Excel - Find & Replace text in a string
 

Here's a modified version:

=IF(ISNUMBER(FIND("#the",LOWER(A1))),"The
"&TRIM(LEFT(A1,FIND("#the",LOWER(A1))-1)),A1)


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378874

Excel - Find & Replace text in a string
 

try this and see if it works for you

=IF(ISNUMBER(FIND("#",A1)),"The "&TRIM(LEFT(A1,FIND("#",A1)-1)),A1)

--


Gary


"bklim" wrote in
message ...

Hi Mangesh

Tq vry much for the prompt reply, and i tried it and found another
problem, it will not process title with #the but #The. Please advise.

Regards/BKLim



mangesh_yadav Wrote:
If your text is in A1, then use:
=IF(ISNUMBER(FIND("#The",A1)),"The
"&TRIM(LEFT(A1,FIND("#The",A1)-1)),A1)

Mangesh


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

Excel - Find & Replace text in a string
 

Dear Mangesh & Gary

You guys are really good la, thanx a million.

Regards/BKLi

--
bkli
-----------------------------------------------------------------------
bklim's Profile: http://www.excelforum.com/member.php...fo&userid=2426
View this thread: http://www.excelforum.com/showthread.php?threadid=37887