Free Microsoft Excel 2013 Quick Reference

Replacing comma in numbers with decimal

I am working with an 11000 row spreadsheet and all of the rows in one column have a comma where a decimal should be. For example 6,54 instead of 6.54. How can I replace the comma with the decimal without manually changing it in each row?

Also I have another column that has descriptions in each row. Some of the descriptions begin and or end with weird text. Like. $%@@&L. Is there a way I can highlight the entire row and only remove the strange characters?
thanks.


Post your answer or comment

comments powered by Disqus
Hi,

I am having problem when summing large numbers with decimals. Here is the
sample. Copy these numbers to the excel sheet and sum them.

1,094,997,817.3305
84,045,000.0000
2,238,305,000.0000
35,770,000.0000
737,995,000.0000
187,980,000.0000
7,750,000.0000
172,900,000.0000
7,100,975.0000
94,154,750,000.0000
16,034,600,000.0000
306,545,000.0000
136,240,000,000.0000
91,995,000.0000
564,407,961.0000

Make sure that the decimals are 4 digits. When we sumi this the result is
251,959,141,753.3310. I have only on decimal value 3305. But in the sum the
values is 3310.

Any idea ?

I need a formular (if it can be done) to extract a number with decimal places
onto 2 columns.

Example:
Column A has the number and I want the whole number in B and the decimal in C
A B C
123.456 123 .456

Change in hours with decimal text format
ex:
A1 = 2.79166666666424
the result would be 67:00

=MAX(A1,0)

"callmewelldolunch" wrote:

> I need a formula in excel to only output numbers greater than zero and
> replace any negative number with zero. The spreadsheet I'm setting up will
> be used to controle inventory and when my inventory on an item is greater
> than my par for that item it is ordering a negative number of replacements.
> the problem is then compounded because the negative number of replacement
> items is then used to calculate my future inventory in the spreadsheet.
> Thanks.

I need a formula in excel to only output numbers greater than zero and
replace any negative number with zero. The spreadsheet I'm setting up will
be used to controle inventory and when my inventory on an item is greater
than my par for that item it is ordering a negative number of replacements.
the problem is then compounded because the negative number of replacement
items is then used to calculate my future inventory in the spreadsheet.
Thanks.

Hello,

Is it possible to automate the following find & replace?

Replace "changeme" in the formula
=MIN(F11:Fchangeme)

With the value returned by this formula?
=ROW(OFFSET(B1,COUNTA(A:A)-1,0))

I have data files of various lengths and need to put the final row number in many formulas throughout the spreadsheet.

Thanks.

I'm trying to calculate the total fee between two dates of a service
with a monthly fixed fee. This means that the fee per day is different
depending on the month (or even year if it's February).

Example...

Assume the monthly fixed fee is 1000.

From 05 March (27 days to count) to 20 April (20 days to count)
the calculation would be: 1000 * (27/31 +20/30) = 1537,63
From 05 April (26 days to count) to 20 May (20 days to count):
1000 * (26/30 + 20/31) = 1511,83

I know it's possible to use something like
Datedif(data1;date2;"d")/30,4375 but this uses an average number of
days per month and doesn't produce an exact enough result.

The problem is to find the exact number of months WITH decimals to
multiply the fee with... (keeping in mind also that it can be more than
12 months).

Does anyone know a simple trick to solve this?

Thx

--
effem
------------------------------------------------------------------------
effem's Profile: http://www.excelforum.com/member.php...o&userid=16112
View this thread: http://www.excelforum.com/showthread...hreadid=275586

Hello, I need to find and replace text in a selection with text from another cell, however I need to do this a number of times without replacing previously replaced text.

I found this code by searching the forum and added to it, but it replaces previous changes. Is there any way I can lock the changes.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 30/10/2004 by dungee
'

'
Range("E5:G6").replace Range("e9").Text, Range("e10").Text
Range("E5:G6").replace Range("f9").Text, Range("f10").Text
Range("E5:G6").replace Range("g9").Text, Range("g10").Text
Range("E5:G6").replace Range("h9").Text, Range("h10").Text
Range("E5:G6").replace Range("i9").Text, Range("i10").Text

End Sub

Hope this makes sense.

Thanks, Chris

Hi

This is a really basic question, and my knowledge in VBA reaches far beyond such simple stuff. But which variable type uses numbers with decimal places? I always figured 'LONGS' did this, but they just seems to 'round-up' the numbers.

Thanks
Adam

Is there a formula you can use to replace blanks in a worksheet with zeros
without manually going thru the rows and columns and clicking in the zeros?

I have a list of numbers in in column K. Example as follows:
12345678
12 6567893
65236688
533 5944
903 5234

I want to extract the first 5 characters, not including spaces. The code below is taking the first 5 characters and removing the spaces but it is counting the space as a character. For example...533 5944 returns a value of 5335. It should return a value of 53359. I tried putting the replace function in a different part of formula but can't get it working. Any assistance is appreciated. Thanks.

Sub xx()
    
    For Each ce In Range("A2:A5")

        ce.Offset(0, 27).Value = replace(Left(ce.Offset(, 10), 5), " ", "")

    Next ce
    
End Sub


Hi Folk

I'm looking to replace names in a list with another if it finds the corresponding name in another column....

Column A has the original list, if any name is to be replaced its in column H with the replacement in column I.... Column C shows the new list however if no name is to be replaced the name from column A should show in column C too...

I have used the Index and Match functions to change the names,, but can't work out how to get the names not to be changed in column C

many thanks

Hi,

I have a set of numbers which appears to represent elapsed time in the "mm:ss.0000" format which may or may not be an Excel time format.

Here are a few examples of the numbers with their corresponding time value in square brackets:

6.098 [6.098]
9.377 [9.377]

90.557 [1:30.5574]
92.958 [1:32.9577]

899.803 [14:59.8033]
905.082 [15:05.0820]

I need a formula to convert this set of time codes into either their original format or simply the mm:ss time format, so that 905.082 becomes 15:05.0820 or simply 15:05, while 6.098 remains the same or converts to 00:06

Any ideas?

Thanks!

I need to replace the value in A1 with the entered value in B1. eg, A1=20;
B1=20 now I type 30 into B1 and A1=50. Is this at all posibble? Please help?

i want to be able to key in numbers with zero in front without excel dropping
zeros.

Hi again,
I want to set at my textbox,so that if user enter the number with comma instead of dot,the comma will automatically be changed into my default format(dot).for example,if user type in (86,5),the value will be converted to (86.5). I have read through the relevant previous thread regarding this matter and I wrote the function as below.It happens to be that (86,5) will change to (865.00),not as what it suppose to be.So can anyone correct my function below?I thank all of you in advance.


	VB:
	
 txtdiameter_Change() 
    If txtdiameter = vbNullString Then Exit Sub 
     
    If Not IsNumeric(txtdiameter) Then 
        MsgBox "Numbers Only" 
        txtdiameter = "86" 'default value'
    End If 
     
End Sub 
 
Private Sub txtdiameter_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
    Dim temp 
    temp = txtdiameter.Text 
    With CreateObject("vbscript.regexp") 
        .Pattern = "[^0-9.]" ' pick up other than numbers/period
        .Global = True 
        temp = .Replace(temp, "") ' removes if any
    End With 
    txtdiameter.Text = Format(Val(temp), "#.00") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
greet,

matt

Hi everyone,

I am having problems applying custom format to numbers that need to be formatted.

custom format use is 0",00"

When I apply the custom format above to whole numbers, I achieve the results I want:

0.00
20.00
40.00

becomes

0,00
20,00
40,00

However, when I apply to numbers with decimal places

0.20
0.40
0.60

becomes

0,00
0,00
1,00

anyone knows how I can change the custom format so that the period is replaced with a comma but the decimal values are preserved?

appreciate your help, thanks!

Hi all,

I am looking for a formula based solution that will allow decimal numbers to be pulled from a range of cells. I want the numbers to to be copied to a single cell and seperated by commas. The following layout is what I had in mind if anyone is able to offer help with this:

Sample data:
A1 721.0 Cervical spondylosis w/o myelopathy
A2 Closed FX of C6 vertebra 805.06
A3 952.03 C1-C4 level with central cord syndrome

Desired output:
B1 721.0, 805.06, 952.03

Thank you for any help that you may be able to provide.

Patrick

Hey there,

I got the following table: see below

Now what I want to do, is to replace the numbers with words.

In words this would look something like this

FOR ROW Q:

If Value'Q' = 1 replace by A
If Value'Q' = 2 replace by B

FOR ROW R:

If Value'Q' = 1 and Value 'R' = 1 replace by aa
If Value'Q' = 1 and Value 'R' = 2 repalce by aa'
If Value'Q' = 2 and Value 'R' = 1 replace by bb

etc ...

I hope this explains it.

Cheers and thx a lot.

Philipp

+-------------------------------------------------------------------+
|Filename: TABLE1.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4599 |
+-------------------------------------------------------------------+

--
Emsmaps
------------------------------------------------------------------------
Emsmaps's Profile: http://www.excelforum.com/member.php...o&userid=33265
View this thread: http://www.excelforum.com/showthread...hreadid=530880

Hello Everyone, I am new to the forum. I have been working on creating few macros which I managed fine going through some old threads. Currently I am stuck with one which I can't get working.

I have Sheet1 with the values like below. ( there are a total of 127 rows)
id parent_id name 100298 100266 item 1 100297 9048 item 2 100296 item 3 100295 98 item 4 100290 100171 item 5 100275 item 6 100274 9102 item 7 100273 item 8
Sheet2 is similar to the below. (total of 2290 rows)
id 100298 100298 100298 100098|100297 100098|100297 100098|100297 100098|100297 9003|9158
Only 1 column, but has a | symbol to show multiple id values. I want to replace all the numbers in Sheet2 with the corresponding name mentioned in Sheet1 like "item 1", "item 2" etc. Can someone help me with this?

Your help is much appreciated.

Hey there,

I got the following table: see below

Now what I want to do, is to replace the numbers with words.

In words this would look something like this

FOR ROW Q:

If Value'Q' = 1 replace by A
If Value'Q' = 2 replace by B

FOR ROW R:

If Value'Q' = 1 and Value 'R' = 1 replace by aa
If Value'Q' = 1 and Value 'R' = 2 repalce by aa'
If Value'Q' = 2 and Value 'R' = 1 replace by bb

etc ...

I hope this explains it.

Cheers and thx a lot.

Philipp

Is it possible to create a macro that will take a column with each row only having numbers 1-4 and replacing each number in a row with a specific letter??

i.e.

1=A
2=C
3=G
4=T

Attached is a sample sheet that this would be done to...

I have to transfer numbers from Excel into Word tables each month (accounting reports). The mail merge just doesn't work properly, so it's a manual process. The problem is, there is no comma on the keyboard's keypad. Every time you need to put a comma in, you have to jump over to the main keyboard and it slows you down.

So, I came up with a workaround. In each place where you need a comma, simply type ** twice (hit the multiplication key twice). Once you are done entering numbers, simply Find and Replace the ** with a comma. Ensure you don't use ** in your document. If you are really feeling technical, you can record a macro to do the same.

The trick works GREAT and has saved me a ton of time producing the financials each month!!! If anyone else has any ideas to do the same thing (other than copying the Excel sheet with formats and pasting as a picture), please share!!!

Take care!

Hi,

I am trying to look up a value in aother sheet using vlookup and RC notation but unable to figure out how to use a variable to define the row number because that might be different each time the model is run.

The code using the recorder is this:

	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(RC[-4],'CALC'!R5C1:R339C9,9,0))" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to replace the row numbers with the variables FirstRow and LastRow which I'd defined earlier, so that it is something like this (does not work but explains what I want to do).

	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(RC[-4],'CALC'!RFirstRowC1:RLastRowC9,9,0))" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help much appreciated.

PK


No luck finding an answer? You could always try Google.