number not recognised

[a m spock]

i have received a spreadsheet in which some numbers appear to be entered as
text. i have tried the =value(text) function but am not able to convert them
back to numbers. please help.

 

[EricG]

What do these numbers look like? Is there a single apostrophe at the
beginning of them (i.e. a ' )?

Please provide an example of your data.

Have you tried formatting the cells as "Number"? Are they currently
formatted as "Text" or "General"?

Eric

 

[a m spock]

thanks

1. this is a copy : 4,76,258.33
2. there are no aostrophes.
3. i have tried reformatting

is there any other way i can send a copy of a part of the spreadsheet itself?

i am going mad.

 

[r]

Sub test()
Dim rng As Excel.Range
Set rng = [a1:a10]
rng.Value = rng.Value
End Sub

regards
r

Il mio ultimo lavoro ..
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Jacob Skaria]

Format to General

--Hit Ctrl+H (Replace window)
--From replace window
find , (comma)
replace (blank)
--Hit OK

If this post helps click Yes

 

[EricG]

Looks like you have extra commas in your numbers. As Jacob shows, try
getting rid of all the commas first using the Replace command. Select the
entire range, then Edit/Replace...

In the "Find what:" box, type a "," (comma)
In the "Replace with:" box, type nothing (leave it blank)

Press the "Replace All" button.

Then format those cells either as General or Number.

HTH,

Eric

 

[a m spock]

thanks. just tried it.
it deilvers #VALUE!

Sub test()
Dim rng As Excel.Range
Set rng = [a1:a10]
rng.Value = rng.Value
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

i have received a spreadsheet in which some numbers appear to be entered as
text. i have tried the =value(text) function but am not able to convert them
back to numbers. please help.

 

[a m spock]

many thnaks. you have cured hours of frustration.

 

[JLGWhiz]

Regional settings have to match the number format and vice versa. If you
use European number format, the regional settins must be set to accomodate
that format.

thanks. just tried it.
it deilvers #VALUE!

Sub test()
Dim rng As Excel.Range
Set rng = [a1:a10]
rng.Value = rng.Value
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

i have received a spreadsheet in which some numbers appear to be
entered as
text. i have tried the =value(text) function but am not able to convert
them
back to numbers. please help.