standard to number

**chriscla** · 08-02-2021, 04:02 AM

Hello

A customer sent me an Excel file which was generated by a very old system (BASIC), making it impossible to calculate in the file.
Numbers without decimals are considered as number, but once you have decimals, calculations give the value error.
I've tried every possible method I know to convert the 'decimal' cell to number, but no success.
Does anyone of you know how I can fix this?
Small demo file attached (column C needs to be converted).

Thanks
Chris

**AliGW** · 08-02-2021, 04:12 AM

You can use this:

=VALUE(C2)

or this:

=--C2

or this:

=C2+0

to turn these text values into number values in a formula.

**chriscla** · 08-02-2021, 04:24 AM

Hi Ali

I'd already tried this, no success. Any other suggestions?

Chris

**josephteh** · 08-02-2021, 04:30 AM

Maybe you need to TRIM first = VALUE(TRIM(C2))

**AliGW** · 08-02-2021, 04:35 AM

Thy ALL work in your sample sheet! The first two are better, as the last one (below) leaves you with text (although it will compute).

YOU must have done something wrong! Or you have not provided representative data ...

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1	Artnr VM	Gr	Preis
2	0506/CA36	77	2.06	2.06	=C2+0	6.18	=(C2+0)*3
3	0506/CA99	77	2.06	2.06	=--C3	6.18	=--C3*3
4	0514/CA00	77	2.06	2.06	=VALUE(C4)	6.18	=VALUE(C4)*3
5	0514/CA02	77	2.06
6	0514/CA12	77	2.06
7	0514/CA13	77	2.06
8	0514/CA15	77	2.06
9	0514/CA21	77	2.06

Sheet: Blad1

**chriscla** · 08-02-2021, 04:35 AM

No luck with trimming. When I check the cell properties, it is Standard, not Text.
Does this make any difference? I changed Standard to Text, but no success.

**AliGW** · 08-02-2021, 04:37 AM

Please see post #5.

**chriscla** · 08-02-2021, 04:43 AM

I'm completely losing my mind.
When I copy the formula in the file you sent me, I get the value error (value = waarde).
Attachment 742564
Is this a setting thing?

**davsth** · 08-02-2021, 05:26 AM

You need to attach as per the instructions in the yellow banner. the formula work for me in the sample fiie. It is hard to work out your problem unless we can see a representative file showing the problem

**chriscla** · 08-02-2021, 05:35 AM

I attached a file with my first post, in which I have the problem and where Ali and you don't.
I opened the file Ali sent (post #7) and copied the formula in D2 to D5: #VALUE error.
Even when I retype the formula in D2 (=C2+0), I get the error.
I send the opened and saved as Ali file attached.

Chris

**AliGW** · 08-02-2021, 05:42 AM

I don't think you are entering them properly.

This is YOUR latest workbook:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1	Artnr VM	Gr	Preis
2	0506/CA36	77	2.06	2.06	=C2+0	6.18	=(C2+0)*3
3	0506/CA99	77	2.06	2.06	=--C3	6.18	=--C3*3
4	0514/CA00	77	2.06	2.06	=VALUE(C4)	6.18	=VALUE(C4)*3
5	0514/CA02	77	2.06	2.06	=C5+0
6	0514/CA12	77	2.06	2.06	=C6+0
7	0514/CA13	77	2.06
8	0514/CA15	77	2.06
9	0514/CA21	77	2.06

Sheet: Blad1

**chriscla** · 08-02-2021, 06:07 AM

Attached a screen movie of what I did and what happens.

**AliGW** · 08-02-2021, 06:23 AM

Sorry - I cannot reproduce your issue. It has nothing to do with the formula, and if you put your cursor into those cells and click ENTER they should work.

**chriscla** · 08-02-2021, 07:07 AM

Thanks for looking into it. I'll have to live with it then.
I've tried it in the meantime in Excel 365 and 2019, and they both give the error.

**AliGW** · 08-02-2021, 07:10 AM

I'm sorry - I don't know what more we can do or suggest. Maybe you could ask a friend with a Belgian locale to try it and see if they get the same error: it has to be something system-based.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**rorya** · 08-02-2021, 07:20 AM

Since they are stored as text with a decimal point as the separator, rather than the comma you would use, you could try:

=SUBSTITUTE(C2,".", ",")+0

**chriscla** · 08-02-2021, 07:42 AM

Hi Rory
Thanks. This works!
Chris

**rorya** · 08-02-2021, 07:51 AM

Glad we could help, and thanks for the rep.

standard to number

LinkBack

Thread Tools

Rate This Thread

Display

standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Re: standard to number

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Converting multiple non-standard time formats to one standard?

[SOLVED] Number of Number -- Number 3 as Default Standard

Date extraction from string (not standard is+number answer)

Mobile Number Formatting: E.164 standard

[SOLVED] Converting Standard Time format (hh:mm:ss) to number of minutes

[SOLVED] how to keep in standard sequential number?

Commas, Decimals and Standard Number with nothing -- Output separated data

Bookmarks

Bookmarks

Posting Permissions