Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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=378873 |
#2
|
|||
|
|||
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 -- bklim ------------------------------------------------------------------------ bklim's Profile: http://www.excelforum.com/member.php...o&userid=24268 View this thread: http://www.excelforum.com/showthread...hreadid=378873 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting text qualifier in Excel | Excel Discussion (Misc queries) | |||
Text not copying as text from Word to Excel 2003 | Excel Discussion (Misc queries) | |||
Excel - counting cells containing a text string | Excel Worksheet Functions | |||
Extract hyperlink string from excel cell | Links and Linking in Excel | |||
VB Find and Replace | Excel Worksheet Functions |