Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- bklim ------------------------------------------------------------------------ bklim's Profile: http://www.excelforum.com/member.php...o&userid=24268 View this thread: http://www.excelforum.com/showthread...hreadid=378874 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace a string contained in a link | Excel Discussion (Misc queries) | |||
Find and replace part of a text string | Excel Discussion (Misc queries) | |||
Excel - Find & Replace text in a string | Excel Worksheet Functions | |||
Find/Replace String | Excel Programming | |||
Limitation of Find and Replace Text in Excel | Excel Programming |