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 :confused:

[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

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]

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)

--


Gary


"bklim" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[bklim]

Dear Mangesh & Gary

You guys are really good la, thanx a million.

Regards/BKLim