Usable format

V

veryeavy

Hi all,

I have just received a large data dump and need to convert the following
dollar amounts into a usable format.

0000137708.64+
0000020138.12+
0000081434.24+
0000093316.26+
0000054740.17+

what is the clever way?

Thanks in Advance,

Matt
 
S

Sheeloo

Paste them into A1 in an Excel file...
Choose Data->Text to column...
Choose Fixed width
Click Next
Separate the column just before the + sign
Click FINISH
 
R

Rick Rothstein

Assuming there can be either + or - signs at the end of your data and that
your data starts in A1, put this formula in an unused column on Row 1 and
copy it down...

=--MID(A1&A1,LEN(A1),LEN(A1))

Then Copy it and use Paste Special / Values to paste the converted data back
into Column A and delete the formulas from the formally unused column.
 
R

Rick Rothstein

Of course, if the data is always a fixed 14 characters long, my formula can
be simplified to this...

=--MID(A1&A1,14,14)
 
Top