Excel - Find & Replace text in a string

[bklim]

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

 

[mangesh_yadav]

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

Mangesh

 

[bklim]

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]

Here's a modified version:

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


Mangesh

 

[Gary Keramidas]

try this and see if it works for you

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

 

[bklim]

Dear Mangesh & Gary

You guys are really good la, thanx a million.

Regards/BKLi