How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Jason Jones Your Own Question
Jason Jones
Jason Jones, Computer Expert
Category: Computer
Satisfied Customers: 17945
Experience:  Over 10 years of professional experience.
Type Your Computer Question Here...
Jason Jones is online now

Hi I Have imported data into spreadsheets but the numbers

Customer Question


I Have imported data into spreadsheets but the numbers wont format as numbers or be calculated on

I Need something to convert these into real numbers on these sheets and future spreadsheets

I have no programming skill and normal basic knowledge of excel

Currency pounds sterling
Submitted: 3 years ago.
Category: Computer
Expert:  Jason Jones replied 3 years ago.

Hello and thank you for your question today. My name is Jason. I look forward to helping you today.

May I take a look at the document for you?

Please, be forewarned that the site is not secure:
- Click the following link:
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 3 years ago.

Is it ok to send just a small section of the sheet in the first place?

Expert:  Jason Jones replied 3 years ago.

Yes, sir.

You can send a small section.

Customer: replied 3 years ago.


Expert:  Jason Jones replied 3 years ago.

Thank you.

Which cells & columns should I be looking in?
Customer: replied 3 years ago.

columns E & F just the numeric filled columns G is not so important

Expert:  Jason Jones replied 3 years ago.

Thank you for the update.

The problem is that you have spaces after the numbers.

If you wish, you can use the following script to remove the trailing spaces:

Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
c = Trim(c)
End Sub

You can select the number columns and then run that script. It will remove the trailing spaces for you.

Customer: replied 3 years ago.
How do I enter / use a script
Expert:  Jason Jones replied 3 years ago.

Which version of Excel are you using?

Actually, if you wish, I can remote into your system right now and show you how to do this, at no extra charge.
Customer: replied 3 years ago.


Expert:  Jason Jones replied 3 years ago.

Thank you for the update.

No extra charge:
May I please, start a remote screen sharing session with you so that I can better help you resolve this problem?

Customer: replied 3 years ago.

i cant do that

Expert:  Jason Jones replied 3 years ago.

Okay, thank you.

Are you using a Mac or PC?

Customer: replied 3 years ago.

mac os X 10.9.3

Expert:  Jason Jones replied 3 years ago.

If you do not wish to use scripts, you can just use the trim function.

You can start off with a new column. Put the following formula in the new column and then copy & Paste it down: =trim(a1)
---> In the above example, I am assuming the data is in the a column. You can change that to the correct one.

Once you do that, you can copy special from the new column and paste it as Value.

Or if you wish, you can send me the full document and I can fix it for you.
Customer: replied 3 years ago.

Hi followed instructions but still not true numerics


I did say i needed this to work with future spreadsheets so I need to be able to replicate and not just this sheet



Expert:  Jason Jones replied 3 years ago.

I have found the issue once more.

It looks like it also has non-breaking space characters that are not seen with the trim function.

Here is the code that will work:

Sub TrimALL()
Application.DisplayAlerts = True
Application.EnableEvents = True
If Application.Calculation = xlCalculationManual Then
MsgBox "Calculation was OFF will be turned ON upon completion"
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

The instructions for enabling visual basic and the editing window are found here:

After you run that code for the chosen cells, it will work.
Customer: replied 3 years ago.

Still wont run when tried on this small sample


have uploaded as before see 484350

Expert:  Jason Jones replied 3 years ago.

I see that you input the macro and all, but you must also RUN the macro.

- Select the number's column.
- Choose to run the Macro.

That will fix it.

It is working in the document you sent me after I ran the Macro.

Customer: replied 3 years ago.
I did run the macro and for columns E and F it will not become a currency and stays on the left hand

can you return the spread sheet if you are sure it is working
Expert:  Jason Jones replied 3 years ago.

Here it is: number problem2.xlsm

You must first select the columns and then run the macro.
Customer: replied 3 years ago.


i am clicking o column E then Tools Macro Sheet 1 Trim all then Run


and nothing happens PS I have added extra rows to the sheet you returned but nothing


what am i doing wrong?

Expert:  Jason Jones replied 3 years ago.

When you click on the E Column, is it selecting the whole column? (Does it highlight)?

Customer: replied 3 years ago.


Expert:  Jason Jones replied 3 years ago.

It should be working then. It works on my end with no problems.

When you opened the document and it asked if you wanted to enable scripting, did you choose to allow it?
Customer: replied 3 years ago.

yes enable macros

Expert:  Jason Jones replied 3 years ago.

In that case, there is no reason for it to not work.

Is the remote option still not a possibility?

I have remoted into computers a few hundred thousand times with no problems.

The remote application is a temporary connection that you can close at any time, if you choose. Once we disconnect, there will be no connection.