UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> Convert Text To Number, Office 2007    
 
   
Sako
post Jun 10 2011, 10:44 AM
Post #1

UtterAccess Guru
Posts: 577
From: Canada



Howdo you convert text to number when the text cell contains a ","
tried all the methods described in the MS knowledgebase.
for instance:
4 234 324,34
I want that to become a number format. It works when i do backspace to remove the commas, but it doesnt work with the substitue function
Go to the top of the page
 
+
Squire4Hire
post Jun 10 2011, 10:59 AM
Post #2

UtterAccess Guru
Posts: 900
From: North of the 49th Parallel



If you store the data as a number without the comma, you should be able to set the 'viewed' data with a defined format that includes the comma. Something like:
Format([TextBox], "####,##")
Go to the top of the page
 
+
Sako
post Jun 10 2011, 11:09 AM
Post #3

UtterAccess Guru
Posts: 577
From: Canada



there is no format() function in excel. that is more access/vba
Go to the top of the page
 
+
Bob G
post Jun 10 2011, 11:22 AM
Post #4

UtterAccess VIP
Posts: 10,460
From: CT



are the contents of the cell really like 4 234 324,34 or something like 324,34
Go to the top of the page
 
+
Sako
post Jun 10 2011, 11:26 AM
Post #5

UtterAccess Guru
Posts: 577
From: Canada



it is really like:
495 560,72
this is why it doesnt recognize as a number, mostly because of the extra spaces that separate the 000's
Go to the top of the page
 
+
Squire4Hire
post Jun 10 2011, 11:27 AM
Post #6

UtterAccess Guru
Posts: 900
From: North of the 49th Parallel



Ooooh! Sorry, my mistake. I didn't realize we were talking about Excel.
The only way I know how to do this is to change the decimal character through your international date/time settings in your Control Panel. Be aware this will change your windows settings for all applications.
If using a comma as a decimal place is a standard for you, I don't see why this shouldn't work.
HTH
Go to the top of the page
 
+
detka123
post Jun 10 2011, 11:33 AM
Post #7

UtterAccess Addict
Posts: 207
From: Chicago



Try Office button (upper left corner) --> Advanced --> Use System separators ---- change dot to comma.
Go to the top of the page
 
+
Sako
post Jun 10 2011, 11:45 AM
Post #8

UtterAccess Guru
Posts: 577
From: Canada



it already is...
Go to the top of the page
 
+
Squire4Hire
post Jun 10 2011, 12:03 PM
Post #9

UtterAccess Guru
Posts: 900
From: North of the 49th Parallel



Sako,
've done a bit of testing and I'm a bit confused as to what you've attempted or what is showing in your spreadsheet. Are you trying to do a calculation with the cell? Is it a matter of formatting? When did you change the system preferences to show a comma instead of a decimal - before or after the data was entered? If it was after, have you changed the cell format from text to number? What error are you getting and can it be replicated?
Go to the top of the page
 
+
detka123
post Jun 10 2011, 12:09 PM
Post #10

UtterAccess Addict
Posts: 207
From: Chicago



Just found another way:
=ROUND(SUBSTITUTE(REPLACE(A1,FIND(",",A1,1),1,".")," ",""),2)
There A1 is your cell address. Looks a bit bulky, sorry.
FIND - finds a position of the comma
REPLACE - replaces "," with "."
SUBSTITUTE - removes spaces
ROUND - is basically converting value to a number
Go to the top of the page
 
+
Sako
post Jun 10 2011, 01:30 PM
Post #11

UtterAccess Guru
Posts: 577
From: Canada



Hi,
tried your formula, but the round() returns an error, but the rest of the code worked.
it returns the #VALUE error
Go to the top of the page
 
+
Bob G
post Jun 10 2011, 01:44 PM
Post #12

UtterAccess VIP
Posts: 10,460
From: CT



i copy and pasted that formula and adjusted for the correct cell i tested and it work
Here is what I have in cell A14 ----- 4 234 324,34
There is the formula i have in cell c14 =ROUND(SUBSTITUTE(REPLACE(A14,FIND(",",A14,1),1,".")," ",""),2)
the result is ....... 4234324.34
I am using excel 2007
Go to the top of the page
 
+
datAdrenaline
post Jun 10 2011, 02:52 PM
Post #13

UtterAccess Editor
Posts: 16,747
From: Northern Virginia, USA



I guess I would just use SUBSTITUTE() twice, then wrap with VALUE() ...
VALUE(SUBSTITUTE(SUBSTITUTE(A1,",",".")," ",""))
Go to the top of the page
 
+
traci19087
post Jun 11 2011, 12:58 PM
Post #14

New Member
Posts: 1



The formula posted by datAdrenaline should work, though if using 2007 or later, I'd probably wrap it with an IFERROR to catch random errors (e.g., if used on blank cells, etc). That is assuming you want to change the comma to a period, of course. Otherwise you would want to replace it with an empty string, as follows:
IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(A1,",","")," ","")),"")
The above will replace all spaces with a zero-length string, thus removing them, and will do the same with all commas. It attempts to convert the resulting number, stored as text, into an actual number - and returns an empty string otherwise (you can change to zero if desired).
Go to the top of the page
 
+
Sako
post Jun 12 2011, 04:08 PM
Post #15

UtterAccess Guru
Posts: 577
From: Canada



thanks guys,
'll have to test this one out on Monday. Will let you guys know!
Go to the top of the page
 
+
Sako
post Jun 13 2011, 11:27 AM
Post #16

UtterAccess Guru
Posts: 577
From: Canada



Hi guys,
oes anyone know why the =substitute() function does not work if the cell is composed of numbers ?
Originally, the cell format is custom: # ##0.00;-# ##0.00;0\.00
So i change it to STANDARD
like if my cell in A1 is: 4 123 321,72 (format is standard)
If I do =substitute (A1," ", "") , or =substitute (A1,"AAA ", "") the result will be the same 4 123 231,72
All I want is to format that cell so that we can do calculations, but nothing seems to work so far
Go to the top of the page
 
+
datAdrenaline
post Jun 13 2011, 11:55 AM
Post #17

UtterAccess Editor
Posts: 16,747
From: Northern Virginia, USA



Please attach as sample spreadsheet with some sample values with the formating you are dealing with and we'll likely be able to help at a deeper level.
Go to the top of the page
 
+
Sako
post Jun 18 2011, 10:29 AM
Post #18

UtterAccess Guru
Posts: 577
From: Canada



here is the attachment,
kept on trying with no success... but something tells me it must be related to the formatting..
Attached File(s)
Attached File  whatswrongwiththisformat.zip ( 8.51K ) Number of downloads: 4
 
Go to the top of the page
 
+
datAdrenaline
post Jun 19 2011, 03:30 PM
Post #19

UtterAccess Editor
Posts: 16,747
From: Northern Virginia, USA



Ok ... here is the issue. The "space" in your text is NOT a standard space (character code of 32), its a NON-breaking space! -- which has a character code of 160 ...
o ... if you use the following expression in cell C1 of the spreadsheet you posted, all will be good in the world! <
=IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(A1,",",""),CHAR(160),"")),"")
Go to the top of the page
 
+
Sako
post Jun 21 2011, 06:56 PM
Post #20

UtterAccess Guru
Posts: 577
From: Canada



Haha,
think we found it at the same time.
how did you find out it was a NON-breaking space?
All I could figure out was that it was NOT a space, so I figured it was some invisible characters...
my formula is similar to yours:
=value(substitute(A1,MID(B2,2,1)))) etc something like that
I just used the MID() function to retrieve that "invisible" character, and then replaced it with a "" everywhere
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 06:59 AM