text to numbers

[billisnice]

I get a report in every day and column A comes in as text with a - sign as the last character

Column A
5498-
75-
32-
90-

I have been using =IF( LEN( A1 )<>0, VALUE(LEFT(A1,LEN(A1)-1)), "" ) to convert to numbers but it does not seem to work all the time. Is there an error with my formula?

Thanks

[Jonmo1]

Try highlighting the problematic numbers
Data - Text To Columns
Deliminated - Next
UNcheck all options - Next
Click Advanced, check "Trailing minus for negative numbers"
OK
Finish

[MrShorty]

Along the same lines, you might look at the process you are using to import these numbers. If you are going through the text import wizard, the same option should be available as part of that dialog. Then you can catch these trailing minus numbers at the point of importation rather than fixing it after the data are imported.

[Jonmo1]

Actually, we may have misunderstood the intention..
Are those numbers supposed to be positive? And the - is actually just superfluous ?

If that's the case, then still Text To Columns, but put a check on Other and enter a - as the Delimiter.

[billisnice]

I can do it manually. The numbers are positive.


72- text would be 72 number

[Tony Valko]

Another option...

Select the range of cells in question

Press the key combo of CTRL + H

The Find/Replace userform opens

Find what: -
Replace with: nothing, leave this blank
Replace All

[billisnice]

That is doing it manually without a formula to do the work. I was looking for a formula to do the work. It works great till I close down and reopen and then it does not work...lol

Thanks

[Jonmo1]

Well the formula you posted works just fine with the examples you posted.
In what way does it not work for you 'sometimes' ?
Do you get an error? What error?

Post an example value that exists in A1 when the formula doesn't work.

This might be a simpler formula
=SUBSTITUTE(A1,"-","")+0


And I'll second MrShorty's thought of looking at your process that brings the data in to begin with.
Maybe it can be adjusted to not include that -

[sandy666]

With VALUE(text) text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.
you can try very similar formula: =IF(LEN(A1)<>0,--LEFT(A1,LEN(A1)-1),"") or if it doesn't work, show us value when formula generate error.

[billisnice]

Later today I will post more info...I have a meeting to attend in a few...

I appreciate the help!

[Jonmo1]

--LEFT will suffer the same fate as VALUE when the value it looks at is not numeric.
You'd have to trap that error with IFERROR or some other error testing function.

[sandy666]

Jonmo1,
but it is shorter and less brackets

[Jonmo1]

@sandy666
The way you posted it seemed like you were saying --LEFT would somehow avoid the #Value! error.
And Shorter <> Better or Faster, only fewer keystrokes.

[sandy666]

Jonmo1,
Are you suggesting that the use -- it is a bad thing?
I have quoted only MS Help
The rest depends on the interpretation
I think it is the academic ongoing discussions

[Jonmo1]

Are you suggesting that the use -- it is a bad thing?

Not at all.
Just that it's not any better or worse than Value. Either way works fine.

I didn't mean to offend you, sorry if you took it that way.

I really only wanted to clarify that --LEFT will not avoid the #Value! Error when the value it evaluates is not numeric.
The way your first post was written implied that it would, and that it may have been the problem with OP's original formula.

[sandy666]

Jonmo1,

I do not feel offended in any way

You are right without a doubt
Maybe I used too much mental shortcut in my first post, but just in this case (--) vs Value is better (mean: shorter, 2 vs 7 keystrokes) , what is not soultion for #VALUE, of course. So I said in next words: show us that case when you get an error. No more, no less.

[Jonmo1]

Fair enough.

But my other point is about "Shorter <> Better"
And I'm NOT saying that yours isn't better (or worse) in any way. I'm completely indifferent on that.

But for future FYI
Just because code A is shorter than code B does not mean A is better or faster or more effective than B.
Many other factors often need to be considered to determine which is better for any given situation.

Example:
=A1+B1
=SUM(A1,B1) or SUM(A1:B1)

SUM has a HUGE advantage over simple +, in that it will ignore text entries in A1 or B1, where the + will result in #Value!

[sandy666]

Of course, but I said:

(...) but just in this case (--) vs Value is better (mean: shorter, 2 vs 7 keystrokes) (...)

I agree with you in principle, that not every time shorter mean better.

[Jonmo1]

We'll just have to agree to disagree, and stop hijacking the thread.

Sorry to OP.

[billisnice]

I want to thank everyone help...It was the 0 coming in that cause me problems....

I ended up using Jonmo1 formula ​=SUBSTITUTE(A1,"-","")+0

Because if text was a 0 it would give me a 0

If for some odd reason o instead of 0 come in as text =IFERROR(SUBSTITUTE(A1,"-","")+0, 0) would subsutute the o to 0 without the error message

Thanks for all help!

[Jonmo1]

You're welcome