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.
  • Go back-and-forth until satisfied
    Rate the answer you receive.
Ask Jess M. Your Own Question
Jess M.
Jess M., Computer Support Specialist
Category: Computer
Satisfied Customers: 9430
Experience:  Computer Hardware & Software Specialist - 10yrs Exp, Web Designer & Developer
49766785
Type Your Computer Question Here...
Jess M. is online now

I have copied figures from a database and they will not work

This answer was rated:

I have copied figures from a database and they will not work in excel, the £ sign is incorrect i think £ 25,00
£ 140,00
£ 0,00
£ 106,99
£ 276,98
JA: When did you last update Excel?
Customer: it on office 365
JA: What have you tried so far with your Excel?
Customer: nothing, unable to fix
JA: Anything else you want the Software Expert to know before I connect you?
Customer: I need to convert the data into figures

Hi, welcome and thank you for your question. My name is Jess and I am glad to assist you today.

I am very sorry about your issues. Few things first:

  1. Are you using a Windows computer right now? A Mac? Or a smartphone?
  2. Is this set of data from the data the only file where figure and currency is NOT working?

 

 

Please let me know by replying to me here so that I can help you further.

Best regards,

Jess

Customer: replied 9 days ago.
windows
Customer: replied 9 days ago.
i would like to do it myself so I can sort another time

Is this set of data from the database the only file where figure and currency is NOT working? Are you saying that you can manually create figures with the currency correctly?

Customer: replied 9 days ago.
yes, the spread sheet was copied from a database

Can you send me a sample Excel file with few data showing the problem? You can attach it here using the paper clip icon or the Add Files button.

Customer: replied 9 days ago.
are

Thank you. Please give me a moment.

I got the file. Can you please tell me what is wrong with the data?

Customer: replied 9 days ago.
if you try to add up the column it does not work
Customer: replied 9 days ago.
if you try to add up the data it does not work, I am sure its a formatiing thing
Customer: replied 9 days ago.
are you still there?
Customer: replied 9 days ago.
Jess, are you there

Yes, I am still here

Customer: replied 9 days ago.
Hi Jess, did you understand my question?
Customer: replied 9 days ago.
How do I format the data so I can add up the column?

Yes. I completely understand your problem now. The main cause of the problem is that, all of your data are NOT figures. They are all TEXT information thus you cannot use arithmetic operations on them.

Customer: replied 9 days ago.
I think the £ sign is text and not the regular excel £ sign
Customer: replied 9 days ago.
can I format ?

Definitely! That is the cause of the problem. Let me check if there is something that can be done in this case. Hold on.

Sorry, but this is not a formatting issue. We need to remove the TEXT part and see if we can convert the figures into numbers.

Customer: replied 9 days ago.
great thanks, How?

Please give me a moment to explore if a formula can be use to do this.

Customer: replied 9 days ago.
thankyou

Hi, sorry for this silly question. Is 0,00 the same with 0.00? Is 2,89 the same with 2.89?

Customer: replied 9 days ago.
Yes

Ok. Thank you. I cannot work with the , version since my Excel always shows the decimal period. So I did a Find and Replace action replacing all commas with period. Then I used formulas to extract the NUMBERS in each cell. That is the time you can format it into currency.

I will send the completed file in a moment.

Customer: replied 9 days ago.
Thank you Jess

Here is the finished file:

http://filesxpress.com/d-e8e71f34

The formula I used in cell C2 is:

=VALUE(RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1))

The RIGHT function will compute the number of characters to the right of the digits and extract them. The VALUE function is needed to convert the extracted numbers which are TEXT into numerical figures.

I hope that helped.

 

Best regards,

Jess

Jess M. and 4 other Computer Specialists are ready to help you