Tech Support Guy banner
Status
Not open for further replies.

formating numbers in Excel spreadsheet

871 views 3 replies 2 participants last post by  draceplace 
#1 ·
Question about formating numbers in Excel spreadsheet


I am exporting data from my HSBC bank account and it says it is
excell format. But it actually produces a comma deliminated column with about 20 rows

I have tried to use the "Text to Columns" tool under the Data tab and this works
but the 2 columns of numbers are in fact text so I can not do calculations on them.

I have uploaded the file it is data1.txt

Do you know how I can import this and convert those columns to numbers ?

Thanks.
 
#2 ·
If you change the name of the file to data1.csv (comma separated values) excel will open the file without going through the text to column wizard. To get the full functionality of excel you will need to 'Save As' and excel workbook. Once there you can select a column, right click, choose format, and pick a number format you like. The Excel workbook often will format for you if it detects numbers under the heading.
 
#3 ·
Hi,

Thanks for your reply.

Actually the file was a .csv I only changed it to .txt to be able to upload
it to this forum.

I did a "save as" workbook but it has not made any difference.
Unfortunately even though the number columns look like
numbers they are still staying as text even after formating the cells as numeric !!

I have uploaded it now as data2.xlsx

Hopefully someone can see what I am doing wrong.

Thanks very much.
 

Attachments

#4 ·
Here's what I've come up with.
1. In a empty cell type a 0
2. do a Cntl C (copy) of the 0 cell
3. Select the range you want to convert
4. Do a 'Paste Special...' with All and "Add" selected.

This will convert the text to numbers in place. Seem if you do an action on the cell like in E2 you can do a =(D2)+0 and it will convert to a number in E2.

The above converts them in place. After doing that I did have to format cells to get 2 decimal places.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top