Removing + or - if they exist in cell

[tomleitch]

I am copying data between some sheets with my vba and some of the cells pasted start with a minus or plus sign.

This is causing them to come up with a name error and also causing errors in my code.

The original data (pasted from another program) has cells starting with plus or minus and this is making excel see them as a formula.

I want to remove these - so when they are pasted, they come out as "=-etc" or "=+etc" so excel is adding the = to the front.

What is the best way to fix this?

Thinking of something like if the first two letters are "=-" or "=+" then remove the first two letters??

Any help appreciated.


Thanks
Tom

 

[Yongle]

Does this formula return what you require?

=VALUE(RIGHT(A2,LEN(A2) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) +1))

 

[tomleitch]

Hi Yongle,

Thanks but no it doesn't.

I've maybe not explained it very well.

I want to manipulate the cell value through VBA of cells.


The cells are written into a scripting dictionary and if they are prefixed with + or - it is causing problems. This is only for a few cells out of thousands, but there will occasionally be this issue.

So if the cell value starts with + or -, it comes up in excel as #NAME and excel makes its formula "=-etc...." or "=+etc....."

So if this is the case then I want to get rid of the formula bit at the front.


Thanks
Tom

 

[Yongle]

Have you tried selecting the range and using Excel Find and Replace?

Find =+ and replace with nothing
Find =- and replace with nothing

It works for me
eg
BEFORE:
cell contents
=+tsts ysdydyd
cell returns
#NAME?

AFTER:
cell contents
tsts ysdydyd
cell returns
tsts ysdydyd

 

[tomleitch]

Thanks Yongle, that's a good idea.