Results 1 to 7 of 7

Thread: Find numbers and replace it with letter "P"

  1. #1
    Seeker randz77's Avatar
    Join Date
    Oct 2015
    Location
    Philippines
    Posts
    14
    Articles
    0
    Excel Version
    2013

    Find numbers and replace it with letter "P"



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi. I have a sheet with letters and numbers. I need to find cells that contains numbers and replace it with letter "P". Any ideas on how to do it faster than using CTRL F?

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    204
    Articles
    0
    Excel Version
    2019
    Do the cells in question only contain numbers or is it an alphanumeric cell. Also, what range of data do you wish to explore for this action? Show us an example of what you have and what you want to happen. A picture will help to get a workable solution.

  3. #3
    Seeker randz77's Avatar
    Join Date
    Oct 2015
    Location
    Philippines
    Posts
    14
    Articles
    0
    Excel Version
    2013
    Hi alansidman, please see sample data below. Range of data would be just within 1 sheet. What I currently do is manually replace the numbers with "P", but it's 1 whole sheet full of data.
    P 96 SL VL P
    SL VL P 124 P
    P P 300 VL SL
    P P P 240 SL
    70 P VL SL P

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    895
    Articles
    0
    Excel Version
    Excel 2013
    If I understand you well, try the formula.

    - If data start in 'A1' cell on Sheet1
    - On the Sheet2 set the formula below (copy across)
    Code:
    =IF(ISNUMBER(Sheet1!A1),"P",Sheet1!A1)
    The result of the formula will be the letter "P" in each cell containing the number.

    If you have many rows, then make quick copies of the Excel formula.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #5
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    204
    Articles
    0
    Excel Version
    2019
    Here is a VBA solution. which assumes your data starts in cell A1
    Code:
    Option Explicit
    
    
    Sub FindP()
        Dim c As Range
        Dim rng As Range
        Set rng = Range("A1").CurrentRegion
        For Each c In rng
            If IsNumeric(c) Then c = "P"
        Next c
    End Sub

  6. #6
    Seeker randz77's Avatar
    Join Date
    Oct 2015
    Location
    Philippines
    Posts
    14
    Articles
    0
    Excel Version
    2013
    Wow! you're a genius alansidman.. Thank you so much!

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    895
    Articles
    0
    Excel Version
    Excel 2013
    @alansidman, great VBA code.

    Another way without VBA:

    1. Select all range data
    2. Open 'Go To' window (F5 key or CTRL+G)
    3. Click 'Special' button
    4. Select 'Constants' radio button
    5. Set 'ON' check box 'Numbers' only
    6. Click 'OK' button
    6a. All cells with number are selected
    7. Press 'F2' key
    8. Enter 'P' letter
    9. Press 'CTRL+Enter' keys
    10. All cells are converted to 'P' letter
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

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