VBA Replace

Printable View

08-11-2011, 05:05 AM
DevanG

VBA Replace

Weird problem.

Excel cell A1, when reading on the top bar says: 2135235-2352-3

I want to remove the dashes. My method is:

[VBA]dim fixstring as string
fixstring = sheets(1).range("A1")
fixstring = Replace(fixstring, "-","")[/VBA]

I stepped through and when fixstring is set to A1, it returns 2135235.
This means the replace won't work, since it has already cut off the second half. The cell is a text format.
08-11-2011, 05:35 AM
Aflatoon

I assume that by 'top bar' you mean the formula bar? Could you post a sample workbook?
08-11-2011, 05:44 AM
Bob Phillips

I just tried it, and I get 213523523523. If I write it back to B1, it shows as 2.13524E+11 in the cell, but the full value in the formula bar. How are you returning the value?
08-12-2011, 05:23 AM
DevanG

Quote:

Originally Posted by xld

I just tried it, and I get 213523523523. If I write it back to B1, it shows as 2.13524E+11 in the cell, but the full value in the formula bar. How are you returning the value?

I haven't even gotten to the excel return yet! haha The problem is that it reads in the fixstring string variable (when i step through the sub) ONLY the numbers before the -, which means i'd only return a part of the previous string and the replace does nothing.
08-12-2011, 05:33 AM
Bob Phillips

That seems to be what you said before, but as I said, it doesn't for me, so there must be something else going on.
08-12-2011, 06:17 AM
DevanG

Quote:

Originally Posted by xld

That seems to be what you said before, but as I said, it doesn't for me, so there must be something else going on.

yea.. I really wish I knew what else to show haha -- the sheet has a list of account numbers that have -'s in them, I try to replace but the string variable only picks up the front of it.

The cell is a text format. The dashes and full number show up in the formula bar as well. Wish I could send the workbook but it is secured at another company that I only visit once a week.
08-12-2011, 06:28 AM
Aflatoon

Do you still have the same issue if you use:
[vba]
Dim fixstring As String
fixstring = sheets(1).range("A1").Text
fixstring = Replace(fixstring, "-","")[/vba]

Note: I would not really expect this to make a difference, but want to check.

Also, I assume it is definitely the first sheet that has the values you want?
08-12-2011, 06:44 AM
DevanG

Quote:

Originally Posted by Aflatoon

Do you still have the same issue if you use:
[vba]
Dim fixstring As String
fixstring = sheets(1).range("A1").Text
fixstring = Replace(fixstring, "-","")[/vba]

Note: I would not really expect this to make a difference, but want to check.

Also, I assume it is definitely the first sheet that has the values you want?

Yes it is the first sheet that I am using, since it definitely recognizes the range, just not the correct number.

I won't be back until Tuesday, so i'll try the .text then. Any more suggestions are very welcome so I can try them all later haha