Negative sign behind number? Need in front

jimmyu125

New Member
Joined
Jan 28, 2010
Messages
18
I am working with data loaded from a PDF, and this was one of the things I am having to deal with. I have tried a few things to move the negative sign in front of the number, but can't seem to get a way to do it to all of them. Just to show you a little more clearly

3.31-
4.53
1.87-
28.49-

I can't do much sorting either because the format of the data is a little weird due to converting a PDF, and I want to be safe.

Thanks in advance
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Select your Column,
Click on Data, then Text to Columns
Select Delimited
Click Next
Unselect all choices
Click Finish
 
Upvote 0
I have been following this thread and have a question about Hotpepper's solution.

Can you explain why that solution works? I expected the Text to Columns to split the data and perhaps place the minus sign in a seperate column. Also I do not understand why the number becomes a negative number after finishing the Text to Colums procedure. The solution works perfectly, but I just don't understand why. I thought Text to Columns splits data into two columns.

I am trying to learn, and an understanding of why this solution works would be helpful.

Thanks,

David
 
Upvote 0
Text to columns can be used for much more than just splitting data. In this instance it is taking a number that is stored in Excel as text and converting it to a number, albeit a negative number for this instance.

But you can also use it for other things such as if you have dates like this:

091231

in many fields stored as text, you can use text to columns and when you get to the 3rd screen I believe you can select the option for YMD and it will convert your text date into an actual date field. This can be very useful especially if you get files out of systems other than Excel that don't store dates the same.

Hopefully that gives you a little help?
 
Upvote 0
That makes sense. Excel is converting the text to a number. I did not realize that was possible with Text to Columns. I knew the orginal data was text, but did not realize Text to Columns could convert it to a number like that. Sounds like I need to study up a little more on Text to Columns.

Thanks for the info.


---dave
 
Upvote 0
Yes text to columns can be very useful and can be used many ways other than probably its thought of purpose. Another thing I use it for, is if I have a column of all 9 digit numbers, you can use the fixed width feature and get say the first 5 characters/numbers and not import the other 4. If you have a need for something like that. Its nice that it gives the do not import option.
 
Upvote 0
When you get to the delimiter tab, instead of clicking Finish, click Next.
Click Advanced.
You will notice there is an option for Trailing minus for negative numbers. which is usually checked by default.
 
Upvote 0
Thanks Hotpepper. This is making more sense. I am starting to understand what is going on now. I had not looked at the settings behind the Advance tab before.

Thanks again!

--dave
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top