+ Reply to Thread
Results 1 to 9 of 9

convert cell text to "80R" if cell has"R" as part of text/

  1. 07-21-2021, 03:15 PM #1
    excelnando
    excelnando is offline
    Registered User
    Join Date
    02-12-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    convert cell text to "80R" if cell has"R" as part of text/

    Hello, I need to convert cell text to "80R-" plus cell content if part of such cell as the letter "R" and convert to "75FS-" if part of cell contains letter "F".

    Ej

    332R5 I need to convert to 80R-332R5
    301FS8 I need to convert to 75FS-301FS8
    Reply With QuoteRegister To Reply
  2. 07-21-2021, 04:29 PM #2
    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: convert cell text to "80R" if cell has"R" as part of text/

    Try this. If neither R nor F is found, it doesn't change the value.

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


    This can be in any column. If you need to change the actual cells instead of showing another cell with the result, then you will need VBA.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 08-02-2021, 11:49 AM #3
    excelnando
    excelnando is offline
    Registered User
    Join Date
    02-12-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: convert cell text to "80R" if cell has"R" as part of text/

    Partially Solved! Thanks!
    Reply With QuoteRegister To Reply
  4. 08-02-2021, 11:58 AM #4
    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: convert cell text to "80R" if cell has"R" as part of text/

    What is the part is not solved?
    Reply With QuoteRegister To Reply
  5. 08-02-2021, 05:57 PM #5
    excelnando
    excelnando is offline
    Registered User
    Join Date
    02-12-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: convert cell text to "80R" if cell has"R" as part of text/

    A =IF(ISNUMBER(FIND("R",A1)),"80R-"&A1,ISNUMBER(FIND("F",A1)),"75FS-"&A1,A1))

    B =IF(ISNUMBER(FIND("R",K42)),"80R-"&K42)

    I was able to run part "B".
    Reply With QuoteRegister To Reply
  6. 08-02-2021, 06:34 PM #6
    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: convert cell text to "80R" if cell has"R" as part of text/

    OK what happens if you use formula A? Please provide some sample data, and show what result you want.
    Reply With QuoteRegister To Reply
  7. 08-02-2021, 07:43 PM #7
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: convert cell text to "80R" if cell has"R" as part of text/

    Jeff, did you need to add a second if to it like this...
    =IF(ISNUMBER(FIND("R",A2)),"80R-"&A2,IF(ISNUMBER(FIND("F",A2)),"75FS-"&A2,A2))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  8. 08-03-2021, 10:07 AM #8
    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: convert cell text to "80R" if cell has"R" as part of text/

    Quote Originally Posted by Sam Capricci View Post
    Jeff, did you need to add a second if to it like this...
    Oops. That looks right. Thanks.
    Reply With QuoteRegister To Reply
  9. 08-03-2021, 12:19 PM #9
    excelnando
    excelnando is offline
    Registered User
    Join Date
    02-12-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: convert cell text to "80R" if cell has"R" as part of text/

    Right on, that worked like a charm! thanks guys!!
    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. Hyperlink click "cell text" to next sheet and auto filter by the "same clicked text" VBA
    By determinedtoexceed in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-26-2021, 06:27 AM
  2. "If Column A contains "text", then display "text" in Cell B1"
    By Cinders in forum Excel General
    Replies: 2
    Last Post: 03-22-2018, 06:34 AM
  3. What's wrong with my this formula "=CELL("contents","D" & TEXT(MATCH(MIN(M25:M39),M25:M39"
    By woshichuanqilz in forum Excel General
    Replies: 4
    Last Post: 06-11-2017, 02:03 PM
  4. create text string: "NOT "[b2value]", NOT "[b3value]", ..., NOT "[b600value]"
    By savekels in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-15-2014, 06:00 AM
  5. Find "TEXT 1" in a range and enter "TEXT 2" in the adjacent cell
    By madreag in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-28-2013, 05:34 PM
  6. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  7. How to filter for word in text cell? ie "mental" not "environmental"
    By duff in forum Excel General
    Replies: 2
    Last Post: 08-02-2011, 06:57 AM

Tags for this Thread

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