Usable format

  • Thread starter Thread starter veryeavy
  • Start date Start date
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
 
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
 
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.
 
Of course, if the data is always a fixed 14 characters long, my formula can
be simplified to this...

=--MID(A1&A1,14,14)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

convert Julian Date Format to Excel 1
Converting yyyymm and yyyymmdd to "mmm dd, yyyy" 3
Outlook from .dotx to .oft 1
Converting a non recognizable date time 1
Date Format Question 1
Cut & Paste in Excel without erasing cell borders? 1
Dollar amounts aren't merging from Excel into a Word document... 3
Open/Import MS Money OFX file into Excel? 3

Back
Top