Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Globally deleting part of the information in a cell
I have many rows of data. In several cells, the end of the data in the cell
ends with a period, some numbers and an x. So the information to be deleted would like something like .035x where 035 could be any number. Since there is other data in any given cell, I would like to delete everything in a cell from the period and anything following it. Is there a way to do that? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Globally deleting part of the information in a cell
We need to be careful we do not convert real numbers like 12.55 to 12
Let's say your first entry is in A1. This will make the conversion =IF(AND(ISNUMBER(FIND(".",A1)),ISTEXT(A1)),LEFT(A1 ,FIND(".",A1)-1),A1) Type this in the first cell in the second blank row after your "many rows of data" Copy it across and down to get results for all your data Select all the cells with the formula and Copy. With them still selected, use Edit | Paste Special with Values specified. This turns all the formula to values. You can now delete all the original data Experiment on a copy of you workbook! best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JanetP" wrote in message ... I have many rows of data. In several cells, the end of the data in the cell ends with a period, some numbers and an x. So the information to be deleted would like something like .035x where 035 could be any number. Since there is other data in any given cell, I would like to delete everything in a cell from the period and anything following it. Is there a way to do that? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Globally deleting part of the information in a cell
Select the cells that you want to modify and run this macro:
Sub CleanUp() Dim s As String For Each r In Selection v = r.Text If InStr(v, ".") = 0 Then Else r.Value = Split(v, ".")(0) End If Next End Sub 213 will be left alone 213.456 will become 213 213.07qwerty will become 213 -- Gary''s Student - gsnu200814 "JanetP" wrote: I have many rows of data. In several cells, the end of the data in the cell ends with a period, some numbers and an x. So the information to be deleted would like something like .035x where 035 could be any number. Since there is other data in any given cell, I would like to delete everything in a cell from the period and anything following it. Is there a way to do that? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Globally deleting part of the information in a cell
If there is no chance of a second period in the entire cell then this
formula will return the part up to and not including the period: =LEFT(A2,FIND(".",A2)-1) If there is a chance of more periods in the same cell then we need a far more complex formula. HTH Kostis Vezerides On Nov 19, 4:51*pm, JanetP wrote: I have many rows of data. *In several cells, the end of the data in the cell ends with a period, some numbers and an x. *So the information to be deleted would like something like .035x where 035 could be any number. *Since there is other data in any given cell, I would like to delete everything in a cell from the period and anything following it. Is there a way to do that? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Globally deleting part of the information in a cell
Sub deletepastperiodincell()
For Each c In Range("g1:g11") x = InStr(CStr(c), ".") If x 0 Then c.Value = Left(c, x - 1) Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "JanetP" wrote in message ... I have many rows of data. In several cells, the end of the data in the cell ends with a period, some numbers and an x. So the information to be deleted would like something like .035x where 035 could be any number. Since there is other data in any given cell, I would like to delete everything in a cell from the period and anything following it. Is there a way to do that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting part of the infomation in a cell | Excel Discussion (Misc queries) | |||
deleting information from a cell | Excel Discussion (Misc queries) | |||
moving part of information from one cell to another... | Excel Worksheet Functions | |||
Deleting time part of a Date, subtracting dates | Excel Discussion (Misc queries) | |||
Deleting part of a cell | Excel Discussion (Misc queries) |