+ Reply to Thread
Results 1 to 9 of 9

replace some part of text in cell with prefix

  1. 02-27-2017, 02:04 AM #1
    sepreta
    sepreta is offline
    Registered User
    Join Date
    02-23-2017
    Location
    london
    MS-Off Ver
    2013
    Posts
    6

    Post replace some part of text in cell with prefix

    hi ,


    I have cells I following format :

    10000dr
    20000cr
    5000dr
    3000cr


    I want to replace dr with "-" sign in prefix
    and cr with "+" or null .

    so that the output must be like this :


    -10000
    +20000
    -5000
    +3000




    thanks In advace
    Reply With QuoteRegister To Reply
  2. 02-27-2017, 02:07 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: replace some part of text in cell with prefix

    Assuming you have data in A1
    Try

    B1
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by shukla.ankur281190; 02-27-2017 at 02:11 AM.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 02-27-2017, 02:19 AM #3
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,939

    Re: replace some part of text in cell with prefix

    have to use vba (I think data was imported from tally or accounting package)
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  4. 02-27-2017, 02:35 AM #4
    sepreta
    sepreta is offline
    Registered User
    Join Date
    02-23-2017
    Location
    london
    MS-Off Ver
    2013
    Posts
    6

    Re: replace some part of text in cell with prefix

    thanks but it is replacing all with '+'
    Reply With QuoteRegister To Reply
  5. 02-27-2017, 02:37 AM #5
    sepreta
    sepreta is offline
    Registered User
    Join Date
    02-23-2017
    Location
    london
    MS-Off Ver
    2013
    Posts
    6

    Re: replace some part of text in cell with prefix

    yes it is imported from tally
    Reply With QuoteRegister To Reply
  6. 02-27-2017, 02:51 AM #6
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: replace some part of text in cell with prefix

    Quote Originally Posted by sepreta View Post
    yes it is imported from tally
    Have look at attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 02-27-2017, 05:04 AM #7
    sepreta
    sepreta is offline
    Registered User
    Join Date
    02-23-2017
    Location
    london
    MS-Off Ver
    2013
    Posts
    6

    Re: replace some part of text in cell with prefix

    Quote Originally Posted by shukla.ankur281190 View Post
    Have look at attached file.
    thanks alot ! it worked
    Reply With QuoteRegister To Reply
  8. 02-27-2017, 05:36 AM #8
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,992

    Re: replace some part of text in cell with prefix

    Glad to know that !!!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  9. 02-27-2017, 06:04 AM #9
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,646

    Re: replace some part of text in cell with prefix

    Another option:

    =1*(IF(RIGHT(A2,2)="dr","-",0)&LEFT(A2,LEN(A2)-2))
    Quang PT
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Find and Replace part of text in cell with a meaning from another worksheet
    By jiwaniakbar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-14-2013, 04:21 PM
  2. [SOLVED] Code to replace text as a part match
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-02-2012, 06:18 AM
  3. Replace one prefix with another prefix
    By uglyduck in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-23-2010, 08:24 PM
  4. Find part of text within cell and replace from another cell
    By sydinstaller in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-27-2008, 05:32 AM
  5. Adding prefix text to cell value
    By auntsally in forum Excel General
    Replies: 3
    Last Post: 03-26-2008, 06:00 AM
  6. Removing a Prefix from Text in a Cell
    By inktec in forum Excel General
    Replies: 6
    Last Post: 04-10-2007, 04:25 PM
  7. Find and replace part of a text string
    By edwin.villamayor@citigroup.com in forum Excel General
    Replies: 2
    Last Post: 07-10-2006, 05:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1