Delete part of a cell value

[Mike]

I need to delete part of a cell value.
Example:
Cell a1 value = Discover*1
Cell a2 value = Discover*2
Cell a3 value = Discover*25

I need it to delete everything from the * right.
Result:
Cell a1 value = Discover
Cell a2 value = Discover
Cell a3 value = Discover

Right now I am using a script that inserts a column and splits the value at
the *. It moves the value right of the * to a new column then I delete that
column.

I can no longer do this. I just need to delete the * and everything to the
right.

Here is my current code:

Public Sub BreakNameApart()

Dim intLocation As Integer, shttest As Worksheet, rngCell As Range
Set shttest = Application.Workbooks("extractnumbers.xls").Worksheets("test")

shttest.Columns("B").Insert

Set rngCell = shttest.Range("a1")
Do Until rngCell.Value = ""
intLocation = InStr(1, rngCell.Value, "*")
rngCell.Offset(columnoffset:=1).Value = Left(String:=rngCell.Value,
Length:=intLocation - 1)
rngCell.Value = Mid(String:=rngCell.Value, Start:=intLocation + 1)
Set rngCell = rngCell.Offset(rowoffset:=1)
Loop

Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

End Sub

Any help would be appreciated

 

[Debra Dalgleish]

You could use text to columns without creating a new column. In the
following code, the second column (from the * right) is not imported:

Columns("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
OtherChar:="*", FieldInfo:=Array(Array(1, 1), Array(2, 9))

 

[Mike]

Thanks for the reply...

I also got it to work this way:

Set rngCell = Range("A1")
Do Until rngCell = Range("")
intLocation = InStr(1, rngCell.Value, "*")
rngCell.Value = Mid(String:=rngCell.Value, Start:=1,
Length:=intLocation - 1)
Set rngCell = rngCell.Offset(rowoffset:=1)
Loop

 

[Edwin Niemoller]

txt2search = ActiveCell.Value

to get the stuff left of the *
ActiveCell.Value = Left(txt2search, InStr(1, txt2search, "*") - 1)

and hence to get the right part of the *
ActiveCell.Value = Right(txt2search, Len(txt2search) - InStr(1, txt2search,
"*"))


Cheers

Edwin