VBA Replace

[DevanG]

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.

[Aflatoon]

I assume that by 'top bar' you mean the formula bar? Could you post a sample workbook?

[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?

[DevanG]

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.

[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.

[DevanG]

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.

[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?

[DevanG]

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