|
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 |
=if(condition(fix, it's ok) |
|
second |
find a way to figure out how many |
=Len(cell) |
|
third |
figure out how to just use all of these |
=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.
viewed
84268
times
