# Converting a neg text "215-" to a number [SOLVED]

• I am working with importing data to Excel 2000. Unfortunately, the database I’m importing from formats negative numbers with the minus sign after the number, example 215- . This results in Excel treating them as text, even though I format the column during its import as a number. The positive numbers do not have any symbol and import fine as numbers. Is there any way to have Excel convert a 215- to a negative number?

• For a formula you could use;

=RIGHT(A1,1)&LEFT(A1,FIND("-",A1,1)-1)

Add this formula next to your column you are having trouble with format it as number then fill down as far as you need to.

For a coded way you could try;

Sub ChangeTextToNumber()
Dim c As Range

For Each c In Selection
If InStr(c, "-") <> 0 Then
Number = Left(c, InStr(c, "-") - 1)
Text = Right(c, Len(c.Value) - InStr(c, "-"))
c = "-" & Text & Number
c.NumberFormat = "0"

End If
Next c
End Sub

You would select the range you wish to change before running this.

Bruce

• Bruce, wouldn't that formula take the last character even it was a digit and stick it at the front ?

=IF(RIGHT(A1,1)="-",("-"&LEFT(A1,FIND("-",A1,1)-1))+0,A1) just to differentiate between a proper number and the weird number

• Good Catch Chris! My formula would return a #Value if the range did not contain the "-".

Thanks for looking out.

Bruce

• PERFECT... SOLVED...THANK YOU!!....... I was running into the # value on the first formula when it ran into valid positive numbers in the column, the last formula solved that issue and works perfectly.

If you have the time I would appreciate it if you could walk through how I read the formula, I'll then have a better understanding on how it operates, so I may be able to use it for other tasks.

Again, THANKS for the quick read and solution.

Ray

• Hi Ray,

Chris will probably explain it better than I can, but here's my take on it:

HTH

• Richie,

couldn't have put it better myself

(I'll just add that when its true, the number brought back can sometimes be text, so the +0 tacked onto the end co-erces it back to numeric so that maths can still be performed on the answer)

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!