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?

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?

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?

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?

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?