Remove prefix letter in a Cell with VBA

Pungie

New Member
Joined
Mar 3, 2021
Messages
9
Office Version
  1. 365
I have a simple spread sheet to run my purchase projections and in each column I have a formula. I use a simple method of adding a "Prefix" letter in front of the number in the cell to remove that cell from the calculation and then remove the "prefix" letter to get the number in cell to be included in the calculation again.

I found a VBA code to

Sub JPegMe()
Dim myCell As Range
For Each myCell In Selection
If Not IsEmpty(myCell) Then _
myCell = "z" & myCell
Next myCell
End Sub

Is there a VBA code I can use to remove the prefix?
 

Attachments

  • Capture.JPG
    Capture.JPG
    50 KB · Views: 18

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
hi and welcome to MrExcel.try this
VBA Code:
Sub DeleteZ()
    Dim myCell As Range
    For Each myCell In Selection
        If Not IsEmpty(myCell) Then myCell = Right(myCell, Len(myCell) - 1)
    Next myCell
End Sub
 
Solution

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe something like this...
VBA Code:
Sub Test()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",MID(@,1+(LEFT(@)=""z""),LEN(@)))", "@", .Address))
  End With
End Sub
 
Last edited:

Pungie

New Member
Joined
Mar 3, 2021
Messages
9
Office Version
  1. 365
hi and welcome to MrExcel.try this
VBA Code:
Sub DeleteZ()
    Dim myCell As Range
    For Each myCell In Selection
        If Not IsEmpty(myCell) Then myCell = Right(myCell, Len(myCell) - 1)
    Next myCell
End Sub
Diddi
Your code worked great at removing the "Z" prefix.
Not to be ungrateful, but while I was using it today with a short key I accidently used it on a cell without a "z" prefix and it changed my number by accident. Is there a way to insert an "If / Then" statement to check and only execute the script if the leading letter is a "Z"
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Diddi
Your code worked great at removing the "Z" prefix.
Not to be ungrateful, but while I was using it today with a short key I accidently used it on a cell without a "z" prefix and it changed my number by accident. Is there a way to insert an "If / Then" statement to check and only execute the script if the leading letter is a "Z"
Did you try the code I posted yet (note that I corrected the column reference from what I posted originally)? It will ignore values without a leading "z" and remove the "z" if it is the first character.
 

Pungie

New Member
Joined
Mar 3, 2021
Messages
9
Office Version
  1. 365
Did you try the code I posted yet (note that I corrected the column reference from what I posted originally)? It will ignore values without a leading "z" and remove the "z" if it is the first character.
Sorry I didn't reply to your last post. I did try your code but it didn't do anything.
I have very little VBA knowledge so I wasn't sure if I was doing something wrong.
I just tested the new code and it does indeed remove the prefix if there is a "Z", however it only works on Column C and it removes '"z" from all cells.

Sorry, I should have been more specific.
I actually have a larger spread sheet with multiple sheets each with 5-10 columns. Each column represents different items I buy that I need to run projections on.
I actually need to be able to select specific cells to remove or add "z" to change what cells to include in my simple sum formulas in each column. Each cell represents incoming purchase orders or outgoing sales orders. Not sure it that makes sense. It's actually looks very convoluted so I made a simple sample sheet as an example. I attached one sheet of my actual excel file.

Diddi's code works perfectly for what I need. I just need it to check if there is a "z" prefix before executing the script to remove the left most character in the cell.
If you know how to tweak Diddi's code to do that I would appreciate it.
 

Attachments

  • Capture.JPG
    Capture.JPG
    178 KB · Views: 5

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Okay, this modification will remove the leading "z" if there is one from the cells you select before running the macro...
VBA Code:
Sub Test()
  With Selection
    .Value = Evaluate(Replace("IF(@="""","""",MID(@,1+(LEFT(@)=""z""),LEN(@)))", "@", .Address))
  End With
End Sub
 

Pungie

New Member
Joined
Mar 3, 2021
Messages
9
Office Version
  1. 365
Okay, this modification will remove the leading "z" if there is one from the cells you select before running the macro...
VBA Code:
Sub Test()
  With Selection
    .Value = Evaluate(Replace("IF(@="""","""",MID(@,1+(LEFT(@)=""z""),LEN(@)))", "@", .Address))
  End With
End Sub
It worked on my single column test spread sheet but when I used it on my live spread sheet it deletes all contents in the cell instead of only the "z"
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
It works fine for me. Are you selecting the entire range you want to "fix" or are you just selecting cells from one column?
 

Pungie

New Member
Joined
Mar 3, 2021
Messages
9
Office Version
  1. 365
It works fine for me. Are you selecting the entire range you want to "fix" or are you just selecting cells from one column?
I'm just selecting one cell. I must have something special in my spread sheet that is conflicting with code. I agree it works for me too on my test spread sheet.

Thanks for trying. I will just use the DeleteZ() code from Diddi. It works. I just need to be careful to only select cells with a "z".
 

Forum statistics

Threads
1,144,369
Messages
5,723,950
Members
422,527
Latest member
TotalBeginner201

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top