Convert dashes to zero - VBA

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I have a contiguous range of data that I plan to do apply a number of different functions to, however, due to source of material, some of the data points will use a "--" in lieu of zero. I would like to see if there is any VBA language that can convert these dashes to use a "0.00" format instead.

So far, I have tried the following:
Range("B1").currentregion.select
selection.numberformat = "0.00"

However, this does not update the dashes to the 0.00 which allows the functions to work properly. Any help would be much appreciated - thank you!
 

Excel Facts

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub NinaE()
   Range("B1").CurrentRegion.Replace "--", 0, xlWhole, , , , False, False
End Sub
or just use Ctrl H on the sheet & replace -- with 0
 

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Unfortunately, I cannot get this to work. My preference would be to find VBA language.

Here's a sample bit of the data:
681116109Ollie's Bargain Outlet Holdings Inc16.38682.99382.99383.0952
68213N109Omnicell, Inc.9.09521.06881.06881.1050
69753M105Palomar Holdings, Inc.36.78430.63020.63020.6515
70438V106Paylocity Holding Corp.26.48471.97651.97652.0434
74758T303Qualys, Inc.17.88592.27352.27352.3505
75606N109RealPage, Inc.21.16223.20483.20483.3133
76029L100Repay Holdings Corp. Class A--3.2753----
759916109Repligen Corporation37.25691.10281.10281.1401
825698103Shyft Group, Inc.9.25782.44722.44722.5301
83417Q105SolarWinds Corp.24.53302.27562.27562.3526
78463M107SPS Commerce, Inc.12.98123.02483.02483.1273
873379101Tabula Rasa Healthcare, Inc.45.35451.57261.57261.6259
87874R100TechTarget, Inc.8.28401.63751.63751.6929
89531P105Trex Company, Inc.16.34411.54381.54381.5961
91544A109Upland Software, Inc.44.75741.77311.77311.8332
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't it work?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are the values you are trying to replace hard-coded values, or are they formulas?

If they are formulas, you probably just need to change the Number Format applied to those columns.

If they are hard-coded values, are there extra spaces before or after the "--"?
An easy way to check is to use the LEN function, i.e. pick a cell returning this (let's say C4), and enter this formula in any blank cell:
Excel Formula:
=LEN(C4)
If it is anything other than 2, you have extra characters that you need to account for in the replace code.
 

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Are the values you are trying to replace hard-coded values, or are they formulas?

If they are formulas, you probably just need to change the Number Format applied to those columns.

If they are hard-coded values, are there extra spaces before or after the "--"?
An easy way to check is to use the LEN function, i.e. pick a cell returning this (let's say C4), and enter this formula in any blank cell:
Excel Formula:
=LEN(C4)
If it is anything other than 2, you have extra characters that you need to account for in the replace code.


They are hard coded values, and I did the =LEN test and it comes back with a 2. So, I'm not sure what I'm doing wrong. I'm sure I'm doing something incorrectly, but I copied the code above directly from the answer and just changed the range cell. ??
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Does your data really begin on row 1?
Are there any completely blank rows or columns in the middle of your data?

What does this line of code return?
VBA Code:
MsgBox Range("B1").CurrentRegion.Address
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
If you use
=Code(C4)
looking at one of those cells, what does it return?
 

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Does your data really begin on row 1?
Are there any completely blank rows or columns in the middle of your data?

What does this line of code return?
VBA Code:
MsgBox Range("B1").CurrentRegion.Address

My data starts in cell C7, but I replaced the "B1" he provided with the C7 reference.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,512
Messages
5,636,764
Members
416,939
Latest member
Rajakumaran

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