+ Reply to Thread
Results 1 to 16 of 16

how do i replace and remove character at the same time in excel

  1. #1
    Registered User
    Join Date
    02-26-2018
    Location
    singapore
    MS-Off Ver
    office 365 mac
    Posts
    9

    Question how do i replace and remove character at the same time in excel

    hi i have a bunch of product code that i wish to shorten and add some character all at the same time. would that be possible?

    eg:
    811C994/2082I
    811C1000/CT220

    i would like to remove the the first 3 character of the product code (i.e.811) and all character after "/" (inclusive of "/" as well)

    eg:
    811C994/2082I -> C994
    811C1000/CT220 -> C1000

    after which i would like to add a bunch of "0" after the first character to create a 7 character product code

    eg:
    C994 -> C000994
    C1000 -> C001000

    Please help. thanks!
    Last edited by bastjanlee; 02-26-2018 at 05:47 AM. Reason: formatting

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: how do i replace and remove character at the same time in excel

    Is this in one cell or two????

    811C994/2082I 811C1000/CT220
    Glenn



  3. #3
    Registered User
    Join Date
    02-26-2018
    Location
    singapore
    MS-Off Ver
    office 365 mac
    Posts
    9

    Re: how do i replace and remove character at the same time in excel

    glenn sorry for the formatting. i've edit my post

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: how do i replace and remove character at the same time in excel

    Try:

    =LEFT(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),1)&TEXT(MID(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),2,255),"000000")

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: how do i replace and remove character at the same time in excel

    Or.... (simpler):

    =MID(A1,4,1)&TEXT(MID(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),2,255),"000000")

  6. #6
    Registered User
    Join Date
    02-26-2018
    Location
    singapore
    MS-Off Ver
    office 365 mac
    Posts
    9

    Re: how do i replace and remove character at the same time in excel

    thanks glenn it worked perfectly!

    however i just realise i have another problem.

    i have some product code which ends with "B"

    eg: 811C1017B/A4539

    how do i keep those product code with "B" in it as well.

    eg: 811C1017B/A4539 -> C001017B

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: how do i replace and remove character at the same time in excel

    Like this:

    =MID(A1,4,1)&RIGHT("0000000"&MID(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),2,255),6)

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,745

    Re: how do i replace and remove character at the same time in excel

    Or try:

    =MID(REPLACE(A1,5,0,REPT(0,11-SEARCH("/",A1))),4,7)

  9. #9
    Registered User
    Join Date
    02-26-2018
    Location
    singapore
    MS-Off Ver
    office 365 mac
    Posts
    9

    Re: how do i replace and remove character at the same time in excel

    hi glenn

    sorry i didn't make myself clearer.
    i should have said that the product code is not limited to 7 character because some product code has a B and it would be 8 character, not 7 anymore

    my example should be:

    eg:
    811C994B/2082I -> C000994B
    811C1000B/CT220 -> C001000B

    811C994/2082I -> C000994
    811C1000/CT220 -> C001000

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: how do i replace and remove character at the same time in excel

    Maybe this:
    =MID(A1,4,1)&RIGHT("0000000"&MID(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),2,255),IF(ISNUMBER(--MID(A1,SEARCH("/",A1)-1,1)),6,7))

  11. #11
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: how do i replace and remove character at the same time in excel

    Here is a UDF
    - see attached file

    OldCodeNewCode.jpg

    Put in Standard module
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  12. #12
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: how do i replace and remove character at the same time in excel

    Not very pretty
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I started with Phuocam's elegant solution before the "B" complication
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by kev_; 02-26-2018 at 08:07 AM.

  13. #13
    Registered User
    Join Date
    02-26-2018
    Location
    singapore
    MS-Off Ver
    office 365 mac
    Posts
    9

    Re: how do i replace and remove character at the same time in excel

    thanks glenn. this is perfect!

    sometimes the product code ends with a C as well, so dev_'s solution with the "if B" code thing wouldn't work

    Quote Originally Posted by Glenn Kennedy View Post
    Maybe this:
    =MID(A1,4,1)&RIGHT("0000000"&MID(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),2,255),IF(ISNUMBER(--MID(A1,SEARCH("/",A1)-1,1)),6,7))

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: how do i replace and remove character at the same time in excel

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  15. #15
    Registered User
    Join Date
    02-26-2018
    Location
    singapore
    MS-Off Ver
    office 365 mac
    Posts
    9

    Re: how do i replace and remove character at the same time in excel

    hi people. i've another question.

    i've got another batch of product code that i would need to shorten. the parameters are the same as before.

    1. retain product code that ends with B and C
    2. product code ending with B or C will have 8 character.
    3. produce code without B or C will have only 7 character.

    E18C000994S2082I -> C000994
    E18C001000SCT220 -> C001000
    E18C001017BSA2037 -> C001017B
    E18H000614CSLH014 -> H000614C

    Please help. and thanks in advance

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: how do i replace and remove character at the same time in excel

    Yep. Try this one:

    =MID(A1,4,1)&LEFT(MID(A1,5,255),IF(OR(MID(MID(A1,5,255),7,1)={"B","C"}),7,6))
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. excel replace character 160
    By kisanvikas2015 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-29-2015, 04:30 AM
  2. how to remove the * special character in excel
    By mohan_srijaya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-14-2013, 06:26 PM
  3. Replies: 3
    Last Post: 11-20-2012, 10:03 PM
  4. Replies: 5
    Last Post: 06-03-2011, 10:00 AM
  5. [SOLVED] How to remove the tab character from an Excel File?
    By epitstick in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2006, 09:30 AM
  6. Replies: 2
    Last Post: 07-26-2005, 02:05 PM
  7. How do I replace * as a character in a string in Excel?
    By nicolegt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2005, 07:06 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1