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.

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.

- Summing large numbers with decimals in Excel
- Split number with decimal places into 2 columns
- Change in hours with decimal text format
- How do I replace negative numbers with zero in Excel.
- How do I replace negative numbers with zero in Excel.
- Replace word in formula with value from another cell
- Difference between two dates in months with decimals
- Find and replace text in range with contents of other cell
- Variable with decimal places
- What formula is used to replace blanks in spreadsheet with zeros?
- Replace space in string with non-space
- Replace names in list with another
- Coverting numbers with decimals to minutes and seconds
- Replacing value in cell with answer of same cell
- Numbers with 0 prefix
- Replace Comma With Dot In TextBox
- Custom Format To Replace Decimal With Comma
- Pull numbers with decimal from text range and copy to single cell with commas
- IF formula in Excel / Replace numbers with 'words'
- Macro to replace numbers in workseet with corresponding values in another worksheet
- IF formula in Excel / Replace numbers with 'words'
- Creating a Marco to Replace a Specific Number with a Specific Letter in a Column
- Tip - Quick Keypad Commas (for numbers) in Word
- Vlookup in another sheet with row number defined by variable

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 ?

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

ex:

A1 = 2.79166666666424

the result would be 67:00

"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.

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.

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.

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

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

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

without manually going thru the rows and columns and clicking in the zeros?

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

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

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!

B1=20 now I type 30 into B1 and A1=50. Is this at all posibble? Please help?

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:greet,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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

matt

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!

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

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

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.

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

i.e.

1=A

2=C

3=G

4=T

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

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!

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: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).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

VB:Any help much appreciated.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

PK