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: 14391
Experience:  Over 10 years of professional experience.
30759776
Type Your Computer Question Here...
Jason Jones is online now

Hi I Have imported data into spreadsheets but the numbers

Customer Question

Hi

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: 2 years ago.
Category: Computer
Expert:  Jason Jones replied 2 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: http://www.wikisend.com
- 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 2 years ago.

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

Expert:  Jason Jones replied 2 years ago.


Yes, sir.

You can send a small section.

Customer: replied 2 years ago.

416220

Expert:  Jason Jones replied 2 years ago.


Thank you.

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

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

Expert:  Jason Jones replied 2 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)
Next
End Sub


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

Customer: replied 2 years ago.
How do I enter / use a script
Expert:  Jason Jones replied 2 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 2 years ago.

14.4.1

Expert:  Jason Jones replied 2 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 2 years ago.

i cant do that

Expert:  Jason Jones replied 2 years ago.


Okay, thank you.

Are you using a Mac or PC?

Customer: replied 2 years ago.

mac os X 10.9.3

Expert:  Jason Jones replied 2 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 2 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

 

Thanks

Expert:  Jason Jones replied 2 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: http://www.techonthenet.com/excel/macros/visual_basic_editor2011.php

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

Still wont run when tried on this small sample


 


have uploaded as before see 484350

Expert:  Jason Jones replied 2 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 2 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 2 years ago.


Here it is: http://wikisend.com/download/493476/Excel number problem2.xlsm

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

sorry


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 2 years ago.

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

Customer: replied 2 years ago.

yes

Expert:  Jason Jones replied 2 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 2 years ago.

yes enable macros

Expert:  Jason Jones replied 2 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.

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • Kamil Anwar

    Kamil Anwar

    Computer Support Specialist

    Satisfied Customers:

    143
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
< Last | Next >
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg Kamil Anwar's Avatar

    Kamil Anwar

    Computer Support Specialist

    Satisfied Customers:

    143
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
  • http://ww2.justanswer.com/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

    James K.

    Consultant

    Satisfied Customers:

    93
    Technical Director of IT Company
  • http://ww2.justanswer.com/uploads/JO/JonTech/2011-10-25_111835_A2c.64x64.jpg Jon-Tech's Avatar

    Jon-Tech

    IS Manager

    Satisfied Customers:

    61
    20 yrs: Computers, Networking
  • http://ww2.justanswer.com/uploads/MI/mikew4950/2011-12-26_1606_mikephoto2s.64x64.jpg MikeW's Avatar

    MikeW

    Computer Engineer

    Satisfied Customers:

    49
    32+ years Computer and Networking Experience
  • http://ww2.justanswer.com/uploads/JA/jamieratliff/2012-2-8_194551_jamie.64x64.jpg jamieratliff's Avatar

    jamieratliff

    Information Systems Manager

    Satisfied Customers:

    21
    Jamie has worked in the Information Technology field for over a decade.
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERleg4q7o8/2012-3-6_203036_meja.64x64.png TheDoctor's Avatar

    TheDoctor

    Software Engineer

    Satisfied Customers:

    16
    Experienced Software Engineer
  • http://ww2.justanswer.com/uploads/FI/FisherEngineering/2012-1-18_194422_Fisher375Headshotcompressed239K.64x64.jpg Bill Fisher's Avatar

    Bill Fisher

    Computer Engineer

    Satisfied Customers:

    1
    Computer system design, networks, integration, general support.
 
 
 

Related Computer Questions