+ Reply to Thread
Results 1 to 4 of 4

Excel formula to remove a certain number of characters IF...

  1. 05-20-2022, 02:54 PM #1
    Justin.yiriley
    Justin.yiriley is offline
    Registered User
    Join Date
    05-04-2021
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    5

    Excel formula to remove a certain number of characters IF...

    Hello All,

    I'm looking for a formula that will see if a cell begins with either "000000", "000001", or "000005", and if so, modify the text like below.

    If text in cell begins with 000000, then display the original value with four zeroes (0000) instead of six zeroes (000000)
    Example: Cell B2 shows 0000008530X4140, so since this cell begins with 000000, then only display 00008530X4140

    If text in cell begins with 000001, then display the original value without the preceding five zeroes (00000)
    Example: Cell B3 shows 000001566626872, so since this cell begins with 000001, then only display 1566626872.

    If text in cell begins with 000005, then display the original value without the preceding five zeroes (00000)
    Example: Cell B4 shows 000005353098164, so since this cell begins with 000005, then only display 5353098164.

    I appreciate any input, thanks!!

    Excel formula to remove a certain number of characters IF....png
    Reply With QuoteRegister To Reply
  2. 05-20-2022, 03:04 PM #2
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,749

    Re: Excel formula to remove a certain number of characters IF...

    =IF( OR(LEFT(B2,6)="000005",LEFT(B2,6)="000001" ), RIGHT(B2,LEN(B2)-5), IF( LEFT(B2,6)="000000", RIGHT(B2,LEN(B2)-2), ""))

    it would be good if you attached a sample spreadsheet as the yellow banner at top of thread asks, rather than an image

    heres is a quick example sheet
    Attached Files Attached Files
    Last edited by etaf; 05-20-2022 at 03:13 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 05-20-2022, 03:08 PM #3
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Excel formula to remove a certain number of characters IF...

    starting in row 2 and copy down.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  4. 05-20-2022, 06:02 PM #4
    alansidman
    alansidman is online now
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,888

    Re: Excel formula to remove a certain number of characters IF...

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future

    crossposted at: https://www.mrexcel.com/board/thread...rs-if.1205669/
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    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. [SOLVED] Formula Remove slashes, insert X in front of first character and remove last to characters
    By Howardc1001 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-29-2020, 03:31 AM
  2. Translating excel formula into VBA - remove characters after last comma.
    By twanbiz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2016, 10:38 PM
  3. [SOLVED] Macro or formula to display the number of characters per line within an Excel cell
    By AGM_carlos in forum Excel General
    Replies: 6
    Last Post: 03-03-2016, 10:02 PM
  4. Formula to Limit the number of Characters and restrict selected characters
    By ylagan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-09-2016, 11:00 AM
  5. Formula to remove unwanted characters
    By adam2308 in forum Excel General
    Replies: 4
    Last Post: 01-17-2013, 04:38 AM
  6. Remove number and special characters from Filename
    By Elmholt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2012, 06:53 PM
  7. remove trailing characters from number
    By maacmaac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-30-2008, 06:22 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