Functions

Question :	Manipluating text and changing to numbers
Description :	If you have ever had numbers that excel sees as text - that can be frustrating enough. Now add the sign after the number instead of before it.

Someone sent me a question the other day. Seems they acquired some old data from an AS400 system. They needed some numbers from the data and it had been saved somehow (possibly wrong - but that was they way they received it) and imported into Excel.

Everything seemed fine at first. But upon closer examination - the negative numbers had the negative sign after the number. They looked like this 513.42- instead of -513.42 which we would expect.

One of the first things I thought of was VBA but thought maybe there would be a quick way to manipulate it within Excel.

I looked at a few functions and threw this together. It makes sense now, but it needed to be approached with small steps in order for me get to this point.

first	if if didn't have a - at the end leave it alone	=if(condition(fix, it's ok)
second	find a way to figure out how many characters were in the cell	=Len(cell)
third	figure out how to just use all of these characters and leave off the last	=left(cell,length-1)

=IF(RIGHT(B2,1)="-",-VALUE(LEFT(B2,LEN(B2)-1)),B2)

	B	C
1	text	converted
2	1322.56-	-1322.56
3
4	32456-	-32456
5	6-	-6
6	1.23-	-1.23
7	2.456-	-2.456
8	2.357	2.357
9
10
11

Notice the text is left justified and the numbers are right justified (default settings). This can come in handy to remember when you are looking for a problem with a formula

One final step to make this usable would be to:
- highlight column C
- copy
- make sure column C is still highlighted
- paste special (choosing value)

Now your numbers will be the values and not the formulas - so we could delete column B after we make sure everything worked alright.