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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,341
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
The best I can tell, diddi's code requires you to select the same amount of cells as my code does (namely, a range of cell that included the cells you wanted to process)... it does not look like his code would work if you only selected one cell.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Pungie

New Member
Joined
Mar 3, 2021
Messages
9
Office Version
  1. 365
The best I can tell, diddi's code requires you to select the same amount of cells as my code does (namely, a range of cell that included the cells you wanted to process)... it does not look like his code would work if you only selected one cell.
Both your code and his both work to select 1 cell in my test spread sheet, but only his works to remove "z" from my live spread sheet (... along with the 5 in front of a cell with 5500 if I select the wrong cell :oops:. I can see it causing some real problems if I click the wrong cell and don't realize it.)
I will probably try to recreate my live spread sheet and see if your code works there. I'm sure it is a remnant of an old VB code or something else since this sheet is going on 15 years old.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,341
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Just to repeat... select a range that includes the cells you want to process (that range can be larger if you want but for efficiency sake, you should not make it gigantic), then run my macro.... only leading Z's will be deleted, nothing else.
 

Pungie

New Member
Joined
Mar 3, 2021
Messages
9
Office Version
  1. 365
Just to repeat... select a range that includes the cells you want to process (that range can be larger if you want but for efficiency sake, you should not make it gigantic), then run my macro.... only leading Z's will be deleted, nothing else.
Your code worked without any problem after I recreated my sheet and copied over the data. It was my spread sheet.
Thanks for all the help!
 

Rick Rothstein

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

ADVERTISEMENT

Great! I am glad that you now have a working solution.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
we can do a quick modify of DeleteZ to allow it to work better for you if you want
 

Pungie

New Member
Joined
Mar 3, 2021
Messages
9
Office Version
  1. 365

ADVERTISEMENT

we can do a quick modify of DeleteZ to allow it to work better for you if you want
Hi Diddi
Rick's solution works but I'm still trying to learn VBA so if it does not take too long I would love to see your solution. To be honest Rick's code was too advance for me to understand and try to manipulate in the future, but it works. 😬😬
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,341
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi Diddi
Rick's solution works but I'm still trying to learn VBA so if it does not take too long I would love to see your solution. To be honest Rick's code was too advance for me to understand and try to manipulate in the future, but it works. 😬😬
Let me see if I can explain what my formula is doing. First off, the Evaluate function simply processes Excel formulas within the VBA environment, but before I can go on, we need to understand a little about Excel formulas. If you give a formula a range of values to deal with, Excel will perform whatever calculation applies to the range one cell at a time and return an array of values. Now you cannot always see that this is the case because formulas return single values to cells, but an array of values is still calculated behind the scenes. Here is an easy demonstration of this. Put a 1 in cell A1, a 2 in cell A2 and a 3 in cell A3. Next put this formula in cell B1...

=5*A1:A3

What is returned by the formula is 5, but the formula actually calculated 5 times each value in the three cells of A1:A3. You can see this by selecting cell B1 and then selecting everything except for the equal sign in the Formula Bar after which you should press the F9 Key. What you will see is this...

{5;10;15}

That is the array of values that Excel calculated for the formula, but it did not know how to show you all three values, so it showed you just the first value. Press the ESC Key to remove the displayed array and return the formula to the Formula Bar. Now select cells B1:B3, then click into the end of the formula in the Formula Bar and press CTRL+SHIFT+ENTER (that keystroke tells the formula to return its array values to the selected array of cells)... voila, the three values of the array you saw when you pressed the F9 Key are now displayed individually in the (array of) cells you had selected.

Okay, with that backdrop, let's return to my code. As I said earlier, the Evaluate function simply processes Excel formulas within the VBA environment. Since the Selection in the With statement is a range of vertical cells, Evaluate will calculate an array of values, one value for each of those cells in Selection. Those calculated values will be placed right back into the cells that the original values came from in a manner similar to what I showed you for the selection B1:B3 after pressing the CTRL+SHIFT+ENTER keystroke.

Now let's look at the formula itself as I assume those @ signs are confusing you somewhat. They are simply place-holders for the Selection's address. Instead of having to concatenate .Address into the formula three times, I put an @ symbol where the address would go and then used VBA's Replace function to put the actual Selection address directly into the formula at each position with the place-holder @ symbol was located.

I hope the above helps you in understanding how my code works.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
Rick is the guru. he answers my questions when i am stuck :)
if you still want my solution i can take a look
 

Pungie

New Member
Joined
Mar 3, 2021
Messages
9
Office Version
  1. 365
Let me see if I can explain what my formula is doing. First off, the Evaluate function simply processes Excel formulas within the VBA environment, but before I can go on, we need to understand a little about Excel formulas. If you give a formula a range of values to deal with, Excel will perform whatever calculation applies to the range one cell at a time and return an array of values. Now you cannot always see that this is the case because formulas return single values to cells, but an array of values is still calculated behind the scenes. Here is an easy demonstration of this. Put a 1 in cell A1, a 2 in cell A2 and a 3 in cell A3. Next put this formula in cell B1...

=5*A1:A3

What is returned by the formula is 5, but the formula actually calculated 5 times each value in the three cells of A1:A3. You can see this by selecting cell B1 and then selecting everything except for the equal sign in the Formula Bar after which you should press the F9 Key. What you will see is this...

{5;10;15}

That is the array of values that Excel calculated for the formula, but it did not know how to show you all three values, so it showed you just the first value. Press the ESC Key to remove the displayed array and return the formula to the Formula Bar. Now select cells B1:B3, then click into the end of the formula in the Formula Bar and press CTRL+SHIFT+ENTER (that keystroke tells the formula to return its array values to the selected array of cells)... voila, the three values of the array you saw when you pressed the F9 Key are now displayed individually in the (array of) cells you had selected.

Okay, with that backdrop, let's return to my code. As I said earlier, the Evaluate function simply processes Excel formulas within the VBA environment. Since the Selection in the With statement is a range of vertical cells, Evaluate will calculate an array of values, one value for each of those cells in Selection. Those calculated values will be placed right back into the cells that the original values came from in a manner similar to what I showed you for the selection B1:B3 after pressing the CTRL+SHIFT+ENTER keystroke.

Now let's look at the formula itself as I assume those @ signs are confusing you somewhat. They are simply place-holders for the Selection's address. Instead of having to concatenate .Address into the formula three times, I put an @ symbol where the address would go and then used VBA's Replace function to put the actual Selection address directly into the formula at each position with the place-holder @ symbol was located.

I hope the above helps you in understanding how my code works.
Hi Rick

Wow. Thanks for the explanation
I will play around with the code a bit and try to see if I can understand it better.

You and Diddi are so awesome.
 

Forum statistics

Threads
1,141,001
Messages
5,703,656
Members
421,309
Latest member
ray crad

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