Find numbers and replace it with letter "P"

R

randz77

New member
Joined
Oct 26, 2015
Messages
14
Reaction score
0
Points
0
Location
Philippines
Excel Version(s)
2013
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?
 
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.
 
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.
P96SLVLP
SLVLP124P
PP300VLSL
PPP240SL
70PVLSLP
 
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.
 
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
 
@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
 
You must log in or register to reply here.
Back
Top